I am trying to set up RBAC in Postgres where I have a database and a schema. I have a few roles provisioner
, service
, mydb_ro
, and mydb_rw
. I ran the following to change default privileges
ALTER DEFAULT PRIVILEGES FOR ROLE provisioner IN SCHEMA mydb_schema
GRANT SELECT ON TABLES TO mydb_ro; -- only read
ALTER DEFAULT PRIVILEGES FOR ROLE provisioner IN SCHEMA mydb_schema
GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO mydb_rw; -- + write, TRUNCATE optional
ALTER DEFAULT PRIVILEGES FOR ROLE provisioner IN SCHEMA mydb_schema
GRANT REFERENCES, TRIGGER ON TABLES TO mydb_rw; -- + TRIGGER
ALTER DEFAULT PRIVILEGES FOR ROLE provisioner IN SCHEMA mydb_schema
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO mydb_rw; -- SELECT, UPDATE are optional
ALTER DEFAULT PRIVILEGES FOR ROLE provisioner IN SCHEMA mydb_schema
GRANT EXECUTE ON FUNCTIONS TO mydb_rw;
this is what I see in default privileges
postgres@mydb=> \ddp
Default access privileges
┌─────────────┬──────────────────┬──────────┬─────────────────────────────────┐
│ Owner │ Schema │ Type │ Access privileges │
├─────────────┼──────────────────┼──────────┼─────────────────────────────────┤
│ provisioner │ mydb_schema │ function │ mydb_rw=X/provisioner │
│ provisioner │ mydb_schema │ sequence │ mydb_rw=rwU/provisioner │
│ provisioner │ mydb_schema │ table │ mydb_ro=r/provisioner ↵│
│ │ │ │ mydb_rw=awdDxt/provisioner │
└─────────────┴──────────────────┴──────────┴─────────────────────────────────┘
But when I am logged in as a user that has the provisioner
role and I create a table, a user that has service
role cannot see it. What am I doing wrong?
I now ran
GRANT mydb_ro TO mydb_rw;
GRANT mydb_rw TO service;
But that did not help with the permission issue.