Default users permissions are bypassed in public schema

[yajo]

Please, answer some short questions which should help us to understand your problem / question better?

• Which image of the operator are you using? registry.opensource.zalan.do/acid/postgres-operator:v1.7.0
• Where do you run it - cloud or metal? Kubernetes or OpenShift? k3s on VM
• Are you running Postgres Operator in production? no
• Type of issue? Security (I tried sending you an email but your system rejected it 🤷‍♂️)

I'm trying to deploy 2 prepared databases (prod and staging) where the public schema has proper owner, writer and reader users. My first attempt has been to declare it like this:

apiVersion: acid.zalan.do/v1
kind: postgresql
metadata:
  name: acid-test-pgcluster-0
  namespace: test-ns-0
spec:
  numberOfInstances: 3
  postgresql:
    version: "13"
  preparedDatabases:
    prod:
      defaultUsers: true
      extensions:
        unaccent: public
      secretNamespace: test-ns-0
    staging:
      defaultUsers: true
      extensions:
        unaccent: public
      secretNamespace: test-ns-0
  teamId: acid
  volume:
    size: 1Gi

Once that's done, I connected to public schema using all these username/password secret pairs:

• prod-owner-user.acid-test-pgcluster-0.credentials.postgresql.acid.zalan.do
• prod-writer-user.acid-test-pgcluster-0.credentials.postgresql.acid.zalan.do
• prod-reader-user.acid-test-pgcluster-0.credentials.postgresql.acid.zalan.do
• staging-owner-user.acid-test-pgcluster-0.credentials.postgresql.acid.zalan.do
• staging-writer-user.acid-test-pgcluster-0.credentials.postgresql.acid.zalan.do
• staging-reader-user.acid-test-pgcluster-0.credentials.postgresql.acid.zalan.do

With all of them, I executed this query, using this build matrix:

• $user: owner, writer, reader
• $db_main: prod, staging (this is the DB used to obtain the secret from those above).
• $db_alt: prod, staging (this is just a raw value)

DROP TABLE IF EXISTS public.table_${user}_${db_main}_${db_alt};
CREATE TABLE IF NOT EXISTS public.table_${user}_${db_main}_${db_alt} (id SERIAL, name CHAR);

Guess what? They all succeeded. All tables got created in both databases:

bash-5.1# psql -d prod -c '\dt'
                            List of relations
 Schema |           Name            | Type  |           Owner            
--------+---------------------------+-------+----------------------------
 public | table_owner_prod_prod     | table | prod_public_owner_user
 public | table_owner_staging_prod  | table | staging_public_owner_user
 public | table_reader_prod_prod    | table | prod_public_reader_user
 public | table_reader_staging_prod | table | staging_public_reader_user
 public | table_writer_prod_prod    | table | prod_public_writer_user
 public | table_writer_staging_prod | table | staging_public_writer_user
(6 rows)

bash-5.1# psql -d staging -c '\dt'
                             List of relations
 Schema |             Name             | Type  |           Owner            
--------+------------------------------+-------+----------------------------
 public | table_owner_prod_staging     | table | prod_public_owner_user
 public | table_owner_staging_staging  | table | staging_public_owner_user
 public | table_reader_prod_staging    | table | prod_public_reader_user
 public | table_reader_staging_staging | table | staging_public_reader_user
 public | table_writer_prod_staging    | table | prod_public_writer_user
 public | table_writer_staging_staging | table | staging_public_writer_user
(6 rows)

It's supposed that writer and reader users are not able to create tables, but they can do it if it is in the public schema.

The expected result would be something like this:

bash-5.1# psql -d prod -c '\dt'
                            List of relations
 Schema |           Name            | Type  |           Owner            
--------+---------------------------+-------+----------------------------
 public | table_owner_prod_prod     | table | prod_public_owner_user
(1 rows)

bash-5.1# psql -d staging -c '\dt'
                             List of relations
 Schema |             Name             | Type  |           Owner            
--------+------------------------------+-------+----------------------------
 public | table_owner_staging_staging  | table | staging_public_owner_user
(1 rows)

Where all other queries should have been forbidden.

I noticed this doesn't happen i.e. with the data schema that is created by default if you don't specify it.

Adding defaultUsers: true to the schema in the YAML declaration and using the schema-restricted permissions didn't change the situation.

[yajo]

Is that some side effect of what is explained here?:

Note that by default, everyone has CREATE and USAGE privileges on the schema public. This allows all users that are able to connect to a given database to create objects in its public schema. Some usage patterns call for revoking that privilege:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

[FxKu]

Yes, I think so. We do not revoke PUBLIC. I guess, that's why it's usually recommended to not use the public schema 😃

[yajo]

However, when explicitly selected as in the example, I find it counterintuitive that a reader can create tables. I think there should be an option to disable public access, because otherwise a reader user is never just a reader, so the safety sensation it gives is fake. As a minimal "fix" this should be documented. El vie., 1 oct. 2021 9:47, Felix Kunde ***@***.***> escribió:

…

Yes, I think so. We do not revoke PUBLIC. I guess, that's why it's usually recommended to not use the public schema 😃 — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#1630 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAHNXDI7QUILHWJF6BR4DBDUEVYRZANCNFSM5FB3TPWQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[JulesLalu]

Hello @yajo,

Did you find a workaround to this issue ? I am also facing it.

Thanks in advance for your help !

[yajo]

Although surprising, this is standard postgres behavior. So far the only workaround is to add permissions by hand.