Ownership of existing schemas

[jerith]

Schemas that already exist when the operator reconciles a database keep their original ownership. This causes problems with permissions management and effectively makes it impossible to use non-OWNER PostgresUsers for any pre-existing schema, including the default public schema that Postgres creates in every database.

Currently, the operator tries to create all schemas in the schemas list in a Postgres CR. For schemas that don't already exist and schemas that do exist but are owned by the operator-managed owner role, this succeeds and the operator sets the appropriate privileges for the reader and writer roles. For schemas that already exist but have a different owner, creation fails with a permission denied error and the reader/write roles never get their privileges.

After running into this problem, I was able to manually run the privilege-grant queries in psql as a user with the owner role, which indicates that merely checking for the existence of a schema before attempting to create it will fix the issue. However, I think it would be a good idea to set the ownership of these schemas so that the result of reconciliation is the same whether they were created by the operator or already existed.

Either way, the public schema is by far the most likely "existing" schema anyone's going to need, which means that most people are going to run into #60 before they get this far.

[nv1-0]

Hi @jerith
I think you face this issue because when the CR is applied on the existing database the Owner for the database changes but for the tables inside it doesn't. Which makes the database say permission denied for query.

I opened an issue for that #57

Also the Pull request which changes the Owner of existing Tables. Hence the Role created by Operator is the Owner of database plus all the tables inside it.

[jerith]

This is similar to #57 in that ownership isn't managed properly, but it applies to schemas rather than tables.

Schema ownership isn't a problem for OWNER users the way table ownership is, because ownership of the database allows all the same operations that ownership of the schema would allow. However, the problem I described above means that READ and WRITE users don't have any access at all to pre-existing schemas without running manual queries to set permissions for operator-managed roles.