The role-reader and role-writer do not have the appropriate permissions.

[Brainpitcher]

Hello there! I am facing a this kind of trouble:

I created a database, created an owner for it, separately create a user

apiVersion: db.movetokube.com/v1alpha1 kind: PostgresUser metadata: name: my-db-user namespace: app annotations: postgres.db.movetokube.com/instance: POSTGRES_INSTANCE spec: role: username database: my-db secretName: my-secret privileges: READ

a role is created in the database, connects to the previously created role role-reader, the user can connect with login and password, but cannot make a select, there are no rights.

From here:

SELECT grantor, grantee, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee = 'role-reader'; grantor | grantee | table_schema | table_name | privilege_type ---------+---------+--------------+------------+---------------- (0 rows)

This creates a database called test-db and a role test-db-group that is set as the owner of the database. Reader and writer roles are also created. These roles have read and write permissions to all tables in the schemas created by the operator, if any.

The problem is that the role-reader and role-writer do not seem to have the appropriate permissions.

[daduskacpokus]

the same trouble

[Brainpitcher]

Hi there! any information on this problem?

[hitman99]

Hi, I'm happy to review the PRs from time to time, but do not have enough time to spare for doing the work myself for now.

[michalschott]

@Brainpitcher This doesn't work, because user which is used by operator is not being added to ${DATABASE}-group. You have to adjust group membership outside of operator. Additionally I believe you have to define .spec.schemas in your postgres definition (based on https://github.com/movetokube/postgres-operator/blob/master/pkg/controller/postgres/postgres_controller.go#L209C25-L234).

@hitman99 probably something easy to fix?

[Brainpitcher]

@Brainpitcher This doesn't work, because user which is used by operator is not being added to ${DATABASE}-group. You have to adjust group membership outside of operator. Additionally I believe you have to define .spec.schemas in your postgres definition (based on https://github.com/movetokube/postgres-operator/blob/master/pkg/controller/postgres/postgres_controller.go#L209C25-L234).

@hitman99 probably something easy to fix?

Thanks for you help and what if the operator works from a super user?

[michalschott]

Same story.

[hitman99]

I still have not looked into it. I'm searching for maintainers that would help with this operator but so far was unable to find anyone willing to help for free. I know that there are small to medium sized companies taking advantage of this operator in their products, but are not willing to dedicate a fraction of their engineering time for maintenance 🫠

[michalschott]

@Brainpitcher This doesn't work, because user which is used by operator is not being added to ${DATABASE}-group. You have to adjust group membership outside of operator. Additionally I believe you have to define .spec.schemas in your postgres definition (based on https://github.com/movetokube/postgres-operator/blob/master/pkg/controller/postgres/postgres_controller.go#L209C25-L234).
@hitman99 probably something easy to fix?

Thanks for you help and what if the operator works from a super user?

Apologies, I might make you confused.

I'm using it with AWS RDS instances so these are bit different than vanilla postgres (user defined at RDS creation time is not really a SUPERUSER in postgres meaning).