Unable to create relations with schema other than postgres

[neelasha-09]

Hi Team,

We are creating the schema: a3sschema with user- a3s and database - a3sdb.
Below is the configuration used to deploy the cluster ,

spec:
  numberOfInstances: 1
  patroni:
    pg_hba:
    - host    all all 0.0.0.0/0 md5
  postgresql:
    version: "12"
  preparedDatabases:
    a3sdb:
      defaultRoles: false
      defaultUsers: false
      schemas:
        a3sschema:
          defaultRoles: false
          defaultUsers: false
  resources:
    limits:
      cpu: 500m
      memory: 500Mi
    requests:
      cpu: 10m
      memory: 100Mi
  teamId: postgres
  tls:
    secretName: ""
  users:
    a3suser:
    - createdb
  volume:
    size: 1Gi

Upon connecting to the database with a3suser user and a3sdb database we create a table.
However we see the table is created with the public schema and not the a3sschema which was created during deployment.

Please see logs below:

a3sdb=> CREATE TABLE student (
a3sdb(>     name            varchar(80),
a3sdb(>     location        point
a3sdb(> );
CREATE TABLE
a3sdb=>
a3sdb=> \dt
         List of relations
 Schema |  Name   | Type  |  Owner
--------+---------+-------+---------
 public | student | table | a3suser
(1 row)

Also we see the numerous roles created even after setting the defaultRoles: false

a3sdb=> \du
                                                                                List of roles
       Role name        |                         Attributes                         |                                       Member of
------------------------+------------------------------------------------------------+---------------------------------------------------------------------------------------
 a3sdb_a3sschema_owner  | Cannot login                                               | {a3sdb_a3sschema_reader,a3sdb_a3sschema_writer}
 a3sdb_a3sschema_reader | Cannot login                                               | {}
 a3sdb_a3sschema_writer | Cannot login                                               | {a3sdb_a3sschema_reader}
 a3sdb_owner            | Cannot login                                               | {a3sdb_a3sschema_owner,a3sdb_reader,a3sdb_writer,a3sdb_reader_user,a3sdb_writer_user}
 a3sdb_owner_user       |                                                            | {a3sdb_owner}
 a3sdb_reader           | Cannot login                                               | {}
 a3sdb_reader_user      |                                                            | {a3sdb_reader}
 a3sdb_writer           | Cannot login                                               | {a3sdb_reader}
 a3sdb_writer_user      |                                                            | {a3sdb_writer}
 a3suser                | Create DB                                                  | {}
 admin                  | Create DB, Cannot login                                    | {a3sdb_owner,a3suser,a3sdb_owner_user}
 postgres               | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 robot_zmon             | Cannot login                                               | {}
 standby                | Replication                                                | {}
 zalandos               | Create DB, Cannot login                                    | {}

We need you support in order to understand how do we create the relations in our database with our created schema and not the public schema.
Also how do we eliminate the extra roles despite setting the values to "false"

Thanks,
Regards
Neelam Sharma

[FxKu]

1. The main idea behind the preparedDatabases feature is, that the operator creates a setup with default owner, reader and writer roles. If you don't want them, I'd suggest to use the "classic" way and list them under the databases key. However, there you cannot list schemas.
2. The defaultRoles flag is only available for schemas. The <db_name>_[owner|reader|writer] roles will always get created.
3. Once created the operator will not remove roles or delete databases. It is up to the user / dba to remove them. So, it does not help to set it to false after you already created the cluster.
4. The operator does not update the database search_path, so you have to write CREATE TABLE a3sschema.student.

[neelasha-09]

Hi Felix, I tried to create the table a3ssechema.but i see permission denied error a3sdb=> CREATE TABLE a3sschema.cities(name varchar(80),location point); ERROR: permission denied for schema a3sschema Please suggest. Regards, Neelam Sharma

…

On Thu, Jun 11, 2020 at 10:43 PM Felix Kunde ***@***.***> wrote: 1. The main idea behind the preparedDatabases feature is, that the operator creates a setup with default owner, reader and writer roles. If you don't want them, I'd suggest to use the "classic" way and list them under the databases key. However, there you cannot list schemas. 2. The defaultRoles flag is only available for schema. The <db_name>_[owner|reader|writer] roles will always be created. 3. Once created the operator will not remove roles or delete databases. It is up to the user / dba to remove them. So, it does not help to set it to false after you already created the cluster. 4. The operator does not update the database search_path, so you have to write CREATE TABLE a3sschema.student. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#1014 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AP5SFQRWSLQX7DXYFVRDD33RWEGE7ANCNFSM4N3QPFCQ> .

[FxKu]

Then you have to grant USAGE and CREATE permission to the role you are using. With roles listed under users you are responsible for setting access privileges.

That's actually the reason we came up with preparedDatabases. The operator creates roles for you with existing default privileges and all you have to do - when using your own users - is granting these default roles to your role.

You want to create tables? GRANT <dbname>_owner TO my_user
You want a read-only user? GRANT <dbname>_reader TO my_ro_user.
You need write-access? GRANT <dbname>_writer TO my_rw_user.

Hope you get the idea now.

[neelasha-09]

Hi Team, The default users (“a3sdb_a3sschema_[owner,writer,reader]_user”) do not have the search_path set accordingly. Our suggestion for the fix is, after the user is created, do also: ALTER ROLE a3sdb_a3sschema_owner_user IN DATABASE a3sdb SET search_path TO a3sschema,public; Maybe we do a PullRequest for this ? Regards, Neelam Sharma

…

On Mon, Jun 15, 2020 at 1:42 PM Felix Kunde ***@***.***> wrote: Then you have to grant USAGE and CREATE permission to the role you are using. With roles listed under users *you* are responsible for setting access privileges. That's actually the reason we came up with preparedDatabases. The operator creates roles for you with existing default privileges and all you have to do - when using your own users - is granting these default roles to your role. You want to create tables? GRANT <dbname>_owner TO my_user You want a read-only user? GRANT <dbname>_reader TO my_ro_user. You need write-access? GRANT <dbname>_writer TO my_rw_user. Hope you get the idea now. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#1014 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AP5SFQTXSFULXPWFS6BBFM3RWXJXBANCNFSM4N3QPFCQ> .

[neelasha-09]

Hi Felix/Team For the suggestion above, *Our suggestion for the fix is, after the user is created, do also:* *ALTER ROLE a3sdb_a3sschema_owner_user IN DATABASE a3sdb SET search_path TO a3sschema,public;* Can we make the changes to add Alter role in below section of 'database.go' file? c.execAlterSchemaDefaultPrivileges(schemaName, schemaOwner, databaseName) if schemaOwner != dbOwner { c.execAlterSchemaDefaultPrivileges (schemaName, dbOwner, databaseName+"_"+schemaName) c.execAlterSchemaDefaultPrivileges (schemaName, schemaOwner, databaseName+"_"+schemaName) } return nil } func (c *Cluster) databaseSchemaNameValid(schemaName string) bool { if !databaseNameRegexp.MatchString(schemaName) { c.logger.Infof("database schema %q has invalid name", schemaName) return false } return true Regards, Neelam Sharma

…

On Mon, Jun 15, 2020 at 8:43 PM Neelam Sharma ***@***.***> wrote: Hi Team, The default users (“a3sdb_a3sschema_[owner,writer,reader]_user”) do not have the search_path set accordingly. Our suggestion for the fix is, after the user is created, do also: ALTER ROLE a3sdb_a3sschema_owner_user IN DATABASE a3sdb SET search_path TO a3sschema,public; Maybe we do a PullRequest for this ? Regards, Neelam Sharma On Mon, Jun 15, 2020 at 1:42 PM Felix Kunde ***@***.***> wrote: > Then you have to grant USAGE and CREATE permission to the role you are > using. With roles listed under users *you* are responsible for setting > access privileges. > > That's actually the reason we came up with preparedDatabases. The > operator creates roles for you with existing default privileges and all you > have to do - when using your own users - is granting these default roles to > your role. > > You want to create tables? GRANT <dbname>_owner TO my_user > You want a read-only user? GRANT <dbname>_reader TO my_ro_user. > You need write-access? GRANT <dbname>_writer TO my_rw_user. > > Hope you get the idea now. > > — > You are receiving this because you authored the thread. > Reply to this email directly, view it on GitHub > <#1014 (comment)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/AP5SFQTXSFULXPWFS6BBFM3RWXJXBANCNFSM4N3QPFCQ> > . >

[FxKu]

I'd prefer to discuss code suggestions in PRs 😃