Postgresql – Postgres default privileges do not seem to work

postgresqlrole

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.

Best Answer

Having the 'provisioner' role is not the same thing as being 'provisioner'.

For your shown default permission to apply, you would have to create the object as 'provisioner' (for example, SET ROLE provisioner; before you create the objects)

Related Solutions

Postgresql – postgres: “alter default privileges” issue

Either you've forgot to add a permission to SELECT in the GRANT below, quoted from the question:

ALTER DEFAULT PRIVILEGES FOR USER access1
   IN SCHEMA access1 GRANT INSERT, UPDATE, DELETE ON TABLES TO access1_rw;

... or, if you really meant that having the role access1_rw doesn't let read the tables even tough it lets write to them, then you must grant the role access1_ro to user1 (in addition to granting the role access1_rw).

Not having either of those has the consequence that user1 was never granted the right to SELECT from tables in the mentioned schema.

Postgresql – Implicit privileges for schema owner

I suppose, that schema owner has implicit privileges to do anything with schema.

No, that is not the case. The manual on GRANT:

PostgreSQL allows an object owner to revoke their own ordinary privileges: for example, a table owner can make the table read-only to themselves by revoking their own INSERT, UPDATE, DELETE, and TRUNCATE privileges. This is not possible according to the SQL standard. The reason is that PostgreSQL treats the owner's privileges as having been granted by the owner to themselves; therefore they can revoke them too. In the SQL standard, the owner's privileges are granted by an assumed entity "_SYSTEM". Not being "_SYSTEM", the owner cannot revoke these rights.

Bold emphasis mine.

There are also no default privileges for public:

PostgreSQL grants default privileges on some types of objects to PUBLIC. No privileges are granted to PUBLIC by default on tables, columns, schemas or tablespaces. (...)

So even the owner needs privileges for a schema - which are given by default! If all you do is:

CREATE SCHEMA test AUTHORIZATION dummy;

Or:

CREATE SCHEMA test;

Then default privileges are NULL, i.e. default to system defaults (which is USAGE and CREATE for the owner of a schema). The manual on GRANT:

If the "Access privileges" column is empty for a given object, it means the object has default privileges (that is, its privileges column is null). Default privileges always include all privileges for the owner, and can include some privileges for PUBLIC depending on the object type, as explained above. The first GRANT or REVOKE on an object will instantiate the default privileges (producing, for example, {miriam=arwdDxt/miriam}) and then modify them per the specified request. Similarly, entries are shown in "Column access privileges" only for columns with nondefault privileges. (Note: for this purpose, "default privileges" always means the built-in default privileges for the object's type. An object whose privileges have been affected by an ALTER DEFAULT PRIVILEGES command will always be shown with an explicit privilege entry that includes the effects of the ALTER.)

If you revoke privileges from the owner (can be done by owner himself or superuser), then the role does not have these privileges. (But the owner can always grant these privileges to himself as well.) And if you assign a new owner, the status is inherited:

-- as user postgres:
CREATE SCHEMA test1;
REVOKE ALL ON SCHEMA test1 FROM postgres;
ALTER schema test1 owner TO dummy;

You'll now see an empty array ({}) instead of NULL:

SELECT nspname AS schema, nspacl AS privileges
FROM   pg_namespace
WHERE  nspname LIKE 'ow%';

(pgAdmin4 reads from the same system table.)

If you did not explicitly revoke USAGE and CREATE, then the new owner has these privileges, too. That's what's confusing in your description. Did you revoke privileges from the old or new owner?

Best Answer

Related Solutions

Postgresql – postgres: “alter default privileges” issue

Postgresql – Implicit privileges for schema owner

Related Question