-
Notifications
You must be signed in to change notification settings - Fork 585
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Users don't get CREATE permissions with postgresVersion: 15 #3568
Comments
@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. |
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 |
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 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
# ... |
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. |
- Adds init-sql-cm.yaml used for created a configmap. Useful if using postgresql 15 - Adds commented out values as an example on how to use the configmap See the following for reasons for this change: CrunchyData/postgres-operator#3568 and https://www.postgresql.org/docs/release/15.0/
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. |
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. |
is there still a official solution, or are we still left alone with these workarounds after a year? |
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
|
Thanks for the quick feedback. We are currently evaluating various Postgres operators for our application. Still, we also wanted to give CrunchyData PGO and CloudNativePG a try. We've noticed the frustration of the community, and for these reasons and more, POG is certainly not on the shortlist. |
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. |
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. |
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:
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. |
When creating a
PostgresCluster
usingpostgresVersion: 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 getERROR: 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:
But this begs the question how users / database management in PGO is supposed to work when using Postgres 15?
The text was updated successfully, but these errors were encountered: