2

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;
zam6ak
  • 7,229
  • 11
  • 46
  • 84

1 Answers1

1

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.

filiprem
  • 6,721
  • 1
  • 29
  • 42
  • 1
    The docs mention "The privileges can be set globally (i.e., for all objects created in the current database), or just for objects created in specified schemas." My issue was that after creating a DB, you have to connect to it before setting default privileges. – zam6ak Jun 30 '17 at 19:14