Users don't get CREATE permissions with postgresVersion: 15

[dbackeus]

When creating a PostgresCluster using postgresVersion: 14 it's possible to use either the default or explicitly created users + databases and have all the expected CRUD privileges enabled for that user.

However when using postgresVersion: 15 we get ERROR: permission denied for schema public when trying to eg. CREATE TABLE ... for the default user and database.

I'm guessing this is related to the following mentioned in the Postgres 15 release notes:

PostgreSQL 15 also revokes the CREATE permission from all users except a database owner from the public (or default) schema.

But this begs the question how users / database management in PGO is supposed to work when using Postgres 15?

[andrewlecuyer]

@dbackeus thanks for reaching out!

As you mentioned, the behavior you're seeing is simply a result of the change you referenced in PG 15.

Is there any specific behavior you're looking here? For instance, is there something else you'd like and/or expect PGO to be doing here?

As you're seeing, our current approach is to simply align with the new PG 15 behavior here, but we're open to thoughts, suggestions, etc. as to how to best approach this.

[saosebastiao]

To clarify, this is an intended behavioral change introduced in PG15, nothing to do with the Postgres Operator.

https://fluca1978.github.io/2022/07/15/PostgreSQL15PublicSchema.html

[dbackeus]

Right. After realizing that perhaps it isn't in the scope of PGO to give a convenient solution for this we figured out that we could solve this via databaseInitSQL.

Eg:

apiVersion: v1
kind: ConfigMap
metadata:
  name: init-sql
data:
  init.sql: |
    \c <database>
    GRANT CREATE ON SCHEMA public TO "<user>";
---
apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: my-cluster
spec:
  postgresVersion: 15
  databaseInitSQL:
    key: init.sql
    name: init-sql
  # ...

[benjaminjb]

Just wanted to take a moment to 👍 this solution (and closing your own issue -- thanks!). Also, I'm creating a ticket to update the docs around this issue / fix.

[pcornelissen]

I don't agree that this has nothing to do with the postgres operator. The PGO has the ability to create users and databases, but starting with pg15 they serve no purpose, as you can't do something with the generated users. So, right now there is no way to use these users without providing an init SQL script. Then you can skip the users functionality completely and create users plus database in the script with the required permissions. In my opinion, the PGO should create users that can do more than logging in... ;-)

[Ramblurr]

The init SQL workaround only works when the cluster and database are created at the same time.

If you use a single PG cluster to serve multiple applications/databases, ad of oh 15, there's no way to create the new users with proper permissions.

[MSandro]

is there still a official solution, or are we still left alone with these workarounds after a year?

[EugenMayer]

I think PGO removed one of the biggest USPs to their operator compared to clusternative pg with dropping that feature.

In addition, they neither seem to listen nor care about the community here since @jkatz left the company.

We are already in the process of integrating and evaluating cnpg. Main reasons for us us

• user management has been neglected and basically made useless with this feature (even though secret exposing is still a win compared to cnpg). (at least https://github.com/CrunchyData/postgres-operator/issues/2935 has been fixed - thanks!)
• upgrading pg versions is a pain and is kept this way to drive commercial support / products. This has been made worse and worse over the time. Examples: https://github.com/CrunchyData/postgres-operator/issues/3516 , https://github.com/CrunchyData/postgres-operator/issues/3144 , https://github.com/CrunchyData/postgres-operator/issues/3666
• the issue queue is practically dead silent on important issues for years
• upcoming issues with the licensing model like https://github.com/CrunchyData/postgres-operator/issues/3836

[MSandro]

Thanks for the quick feedback. We are currently evaluating various Postgres operators for our application. The first thing they tried was StackGres last week, the experience was great. After two days, StackGres was integrated into our Helm Charts and into our ArgoCD pipeline.

Still, we also wanted to give CrunchyData PGO and CloudNativePG a try. Now even after 5 days we are not satisfied with the setup using CrunchyData PGO. When trying it out, we considered a lot of things like configurability, scalability, upgrades, backups and disaster recovery, and more.

We've noticed the frustration of the community, and for these reasons and more, POG is certainly not on the shortlist.

[pcornelissen]

I am using crunchyData PGO for a while now and had a postgres update a couple of weeks ago. Postgres updates are not fun, lots of manual steps. (But it worked) This issue here is annyoing, but no dealbreaker yet, as I have a initcontainer to handle user creation as workaround. But especially Eugens remarks are something to think about. So I will start evaluating other options like StackGres and cnPG soon.

[MSandro]

That's true, this issue is not the crux of the matter why we probably won't use PGO. We solved it with an InitSQL. We tried upgrading from PG 15 to 16, it worked, but with PGO it was really an adventure. Overall, the experience with POG just wasn't as smoth as with StackGres.

[benjaminjb]

Sorry for the confusion re: closing and opening this issue--this issue auto-closed when some code was merged, but that merge didn't correspond to our release cycle.

But now, we're happy to announce that the newly released CPK 5.6.1 has the ability to automatically create schemas for users defined in the postgrescluster spec, without using the initdb solution that we talked about before.

A few notes about this solution:

• the change to the public schema in PG15+ was a disruption to the way a lot of people did work, BUT it did patch a security hole and we didn't want to go against PG best practices and re-open that security hole.
• to provide a schema that a user could use (regardless of when the user was added to the spec), the operator can now create a schema named after the user in any database that user has access to according to the spec. (The docs I link to below go into this decision at some length.)
• this feature is feature-gated, so you'll have to turn it on during the operator installation if you want this behavior.

For more on this feature (and some of the decisions that went into it), see our documentation: https://access.crunchydata.com/documentation/postgres-operator/latest/tutorials/basic-setup/user-management#automatically-creating-per-user-schemas

I think this solution to the problem offers a lot of flexibility and control to the developer, and I hope that some people in the community get some utility out of these changes! As always, please reopen this issue if you want to talk about this topic more or continue the conversation in our Discord server.