Initialization SQL is not being applied

[rhrytskiv]

I'm following you tutorial here: https://access.crunchydata.com/documentation/postgres-operator/5.2.0/tutorial/customize-cluster/#database-initialization-sql
but the sql is not being executed, and there's not echo statement in operator or db pod logs either.
Sql itself is completely valid, if I just log in to db pod and execute it via psql it works and grants permissions.

I've created my configmap: with kube create configmap qa3-nextgen-db-init-sql --from-file=init.sql=./init.sql

Here's my init.sql

\set ON_ERROR_STOP
\echo "EXECUTING DB INIT SCRIPT"

\c partners
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA "public" TO "partners-writer";
GRANT SELECT                         ON ALL TABLES IN SCHEMA "public" TO "partners-reader";

\c organizations
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA "public" TO "organizations-writer";
GRANT SELECT                         ON ALL TABLES IN SCHEMA "public" TO "organizations-reader";

Here's my cluster definition:

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: qa3-nextgen-db
spec:
  image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-14.5-1
  postgresVersion: 14
  databaseInitSQL:
    key: init.sql
    name: qa3-nextgen-db-init-sql
  instances:
    - name: qa3-db-inst
      replicas: 1
      resources:
        requests:
          cpu: 250m
          memory: 256Mi
        limits:
          cpu: 2.0
          memory: 4Gi
      dataVolumeClaimSpec:
        storageClassName: mayastor-1
        accessModes:
        - "ReadWriteOnce"
        resources:
          requests:
            storage: 5Gi
      affinity:
        podAntiAffinity:
          requiredDuringSchedulingIgnoredDuringExecution:
          - topologyKey: kubernetes.io/hostname
            labelSelector:
              matchLabels:
                postgres-operator.crunchydata.com/cluster: qa3-nextgen-db
                postgres-operator.crunchydata.com/instance-set: qa3-db-inst

  backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:ubi8-2.40-1
      metadata:
        annotations:
          sidecar.istio.io/inject: "false"
      repos:
      - name: repo1
        volume:
          volumeClaimSpec:
            storageClassName: mayastor-1
            accessModes:
            - "ReadWriteOnce"
            resources:
              requests:
                storage: 1Gi

  users:
    - name: organizations-owner
      databases:
        - organizations
      options: "CREATEDB"
      password:
        type: AlphaNumeric

    - name:organizations-writer
      password:
        type: AlphaNumeric

    - name:organizations-reader
      password:
        type: AlphaNumeric
    
    - name:partners-owner
      databases:
        - partners
      options: "CREATEDB"
      password:
        type: AlphaNumeric

    - name:partners-writer
      password:
        type: AlphaNumeric

    - name:partners-reader
      password:
        type: AlphaNumeric

[benjaminjb]

Good news -- we recently merged in a PR that will print the output of the init SQL statement and that will be out in the next release.

I'm actually running that updated code locally (using the make deploy-dev target), and I just ran through this scenario, and I think it's working for me. The only thing I had to change in your manifest was I had to put spaces in a few of those name:<some_name_here> lines, but otherwise, I ran it unchanged, and the stdout of the init sql was:

stdout="\"EXECUTING DB INIT SCRIPT\"\nYou are now connected to database \"partners\" as user \"postgres\".\nGRANT\nGRANT\nYou are now connected to database \"organizations\" as user \"postgres\".\nGRANT\nGRANT\n"

And when I log in, I can see those users and think the GRANTs worked, but I'm double-checking that.

[benjaminjb]

Update as I test: Some of those users are missing their databases field

[benjaminjb]

OK, once I fixed the space and the missing databases field, if I psql in as postgres user:

postgres=# \l
                                           List of databases
     Name      |  Owner   | Encoding |   Collate   |    Ctype    |          Access privileges
---------------+----------+----------+-------------+-------------+-------------------------------------
 organizations | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 | =Tc/postgres                       +
               |          |          |             |             | postgres=CTc/postgres              +
               |          |          |             |             | "organizations-owner"=CTc/postgres +
               |          |          |             |             | "organizations-writer"=CTc/postgres+
               |          |          |             |             | "organizations-reader"=CTc/postgres
 partners      | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 | =Tc/postgres                       +
               |          |          |             |             | postgres=CTc/postgres              +
               |          |          |             |             | "partners-owner"=CTc/postgres      +
               |          |          |             |             | "partners-writer"=CTc/postgres     +
               |          |          |             |             | "partners-reader"=CTc/postgres

[rhrytskiv]

Thank you.
Well, \l results looked the same for me as far as I remember but the no permissions for table error was still there for writer & reader users.
I will however try all of that again and come back tomorrow, maybe I missed something.
As for the missing databases field I left out intentionally as it doesn't give them access, at least in my case, as I've said in #3423.