Following User / Database management docs lead to users without privileges

[dbackeus]

When creating a PostgresCluster without any spec.users field, PGO will create a user + database with the same name. When connecting to the database with the created users all the CRUD privileges we would expect are there (able to create tables etc).

However when trying to use the spec.users configuration according to https://access.crunchydata.com/documentation/postgres-operator/v5/tutorial/user-management/ the users created do not appear to get any privileges at all.

Consider the following configuration for a PostgresCluster named test:

spec:
  users:
    - name: test
      password:
        type: AlphaNumeric
      databases:
        - test

What we're expecting here is identical behaviour to the "default user" which would get created by if spec.users wasn't declared, but with password.type set to AlphaNumeric.

However when connecting to the test database with the test user and trying to create a table we get ERROR: permission denied for schema public.

This begs the question how we're supposed to bootstrap users / databases where the users can actually do anything after the cluster is created. The only work-around I found so far was to add options: SUPERUSER but that doesn't seem right.

I'm guessing that things are in fact working as intended but due to lack of documentation it feels like a bug.

Maybe related: #3423

[dbackeus]

Closing in favour of new issue as this seems wholly related to using postgresVersion 15 rather than postgresVersion 14.

[pyr31000]

When creating a PostgresCluster without any spec.users field, PGO will create a user + database with the same name. When connecting to the database with the created users all the CRUD privileges we would expect are there (able to create tables etc).

However when trying to use the spec.users configuration according to https://access.crunchydata.com/documentation/postgres-operator/v5/tutorial/user-management/ the users created do not appear to get any privileges at all.

Consider the following configuration for a PostgresCluster named test:

spec:
  users:
    - name: test
      password:
        type: AlphaNumeric
      databases:
        - test

What we're expecting here is identical behaviour to the "default user" which would get created by if spec.users wasn't declared, but with password.type set to AlphaNumeric.

However when connecting to the test database with the test user and trying to create a table we get ERROR: permission denied for schema public.

This begs the question how we're supposed to bootstrap users / databases where the users can actually do anything after the cluster is created. The only work-around I found so far was to add options: SUPERUSER but that doesn't seem right.

I'm guessing that things are in fact working as intended but due to lack of documentation it feels like a bug.

Maybe related: #3423

Using options: SUPERUSER as work-around is bad if you are using pooling connection with PgBouncer. PgBouncer doesn't allow connection with any SUPERUSER. What is the work-around in this case ?

[dbackeus]

@pyr31000 this is how we worked around it in the end: #3568 (comment)

[pcornelissen]

Using this users field, usually means that this is the only user that works in that database anyway, so the "protection" that is provided by the public schema permission removal is not required. You could add an optional field to the users part of the crd like "allowPublicAccess" or something like that and then just execute the grant, mentioned in #3568's comment.
This way the users section keeps being useful even with postgres version 15 and higher.