CODEWITHSUNDEEP
postgresqlpermissionssql-grant
zam6ak
zam6ak

Reputation: 7259

PostgreSQL - ALTER DEFAULT PRIVILEDGES....SELECT ON TABLES... in all schemas

Is it possible to alter default priviledges on PostgreSQL role, such that role has SELECT on any table in any schema (existing or created in future)

I have 3 roles (app_r is member of app_rw which is member of app_rwc) and I am trying this:

ALTER DEFAULT PRIVILEGES FOR ROLE app_rwc GRANT USAGE ON SCHEMAS TO app_r;
ALTER DEFAULT PRIVILEGES FOR ROLE app_rwc GRANT SELECT ON TABLES TO app_r;

but it does not work.

SELECT has_table_privilege('app_r', 'some_schema.some_table', 'SELECT')
-- false

Is there a way to do this in PG or do I have to use IN SCHEMA and repeat for every schema? (ughhh)

ALTER DEFAULT PRIVILEGES FOR ROLE app_r IN SCHEMA some_schema GRANT SELECT ON TABLES TO app_r;

P.S. I did try running ALTER DEFAULT before (and after also, just to test) creating schemas and tables...

P.P.S My role setup is inspired by this answer and I saw in this answer that is not necessary having to explicitly specify schemas

UPDATE #1 - Here is exact role setup I am using:

CREATE ROLE app_rwc INHERIT CREATEDB CREATEROLE;
CREATE ROLE app_rw INHERIT;
CREATE ROLE app_r  INHERIT;

GRANT app_r TO app_rw;
GRANT app_rw TO app_rwc;

-- these must be performed *before* any objects are created
ALTER DEFAULT PRIVILEGES FOR ROLE app_rwc GRANT USAGE ON SCHEMAS TO app_r;
ALTER DEFAULT PRIVILEGES FOR ROLE app_rwc GRANT SELECT ON TABLES TO app_r; 
ALTER DEFAULT PRIVILEGES FOR ROLE app_rwc GRANT INSERT, UPDATE, DELETE ON TABLES TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_rwc REVOKE TRUNCATE ON TABLES TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_rwc GRANT SELECT, UPDATE ON SEQUENCES TO app_rw;

Upvotes: 2

Views: 673

Answers (1)

filiprem
filiprem

Reputation: 7144

Is it possible to alter default priviledges on PostgreSQL role, such that role has SELECT on any table in any schema (existing or created in future)

As of version 9.6, no.

Upvotes: 1

Related Questions