Schema level SELECT on all new tables

Hi,

I am trying to work on a scenario , where one user creates tables in a 'schema', and all such tables should be visible to a specific user/role, even if the tables are created in future (after grant SELECT command).

My current flow is as:

USERS: dbadmin, user1, user2
SCHEMA: myschema

dbadmin=> GRANT ALL ON SCHEMA  myschema  to  user1;
dbadmin=> GRANT SELECT  ON  ALL  TABLES  IN  SCHEMA  myschema  TO  user2;

dbadmin=> ALTER  SCHEMA  myschema   DEFAULT   INCLUDE  PRIVILEGES;


--  user1  now   creates  table
user1=> CREATE   TABLE  myschema.table1  (id  INT);

-- can user2 see that table
user2=> SELECT  *  FROM  myschema.table1;
--  Permission Denied for table1

Any ideas on how this can be solved ?
Thanks

Comments

  • edited May 2017

    When inherited privileges are enabled on a schema, all privileges granted to the schema are automatically granted to all newly created tables or views in the schema. Note that you still must grant privileges on the schema to a user. Let me know if this is what you're looking for:

    dbadmin=> create user user1;
    CREATE USER
    dbadmin=> create user user2;
    CREATE USER
    dbadmin=> create schema myschema DEFAULT INCLUDE PRIVILEGES;
    CREATE SCHEMA
    dbadmin=> GRANT ALL ON SCHEMA myschema to user1;
    GRANT PRIVILEGE
    dbadmin=> \x
    Expanded display is on.
    dbadmin=> select * from grants where object_name = 'myschema';
    -[ RECORD 1 ]----------+----------------------------------------------------------------------------
    grant_id               | 45035996274153768
    grantor_id             | 45035996273704962
    grantor                | dbadmin
    privileges_description | INSERT*, SELECT*, UPDATE*, DELETE*, REFERENCES*, USAGE*, CREATE*, TRUNCATE*
    object_schema          |
    object_name            | myschema
    object_id              | 45035996274153766
    object_type            | SCHEMA
    grantee_id             | 45035996273704962
    grantee                | dbadmin
    -[ RECORD 2 ]----------+----------------------------------------------------------------------------
    grant_id               | 45035996274153770
    grantor_id             | 45035996273704962
    grantor                | dbadmin
    privileges_description | USAGE, CREATE
    object_schema          |
    object_name            | myschema
    object_id              | 45035996274153766
    object_type            | SCHEMA
    grantee_id             | 45035996274153758
    grantee                | user1
    
    dbadmin=> grant usage, select on schema myschema to user2;
    GRANT PRIVILEGE
    dbadmin=> select * from grants where object_name = 'myschema';
    -[ RECORD 1 ]----------+----------------------------------------------------------------------------
    grant_id               | 45035996274153768
    grantor_id             | 45035996273704962
    grantor                | dbadmin
    privileges_description | INSERT*, SELECT*, UPDATE*, DELETE*, REFERENCES*, USAGE*, CREATE*, TRUNCATE*
    object_schema          |
    object_name            | myschema
    object_id              | 45035996274153766
    object_type            | SCHEMA
    grantee_id             | 45035996273704962
    grantee                | dbadmin
    -[ RECORD 2 ]----------+----------------------------------------------------------------------------
    grant_id               | 45035996274153770
    grantor_id             | 45035996273704962
    grantor                | dbadmin
    privileges_description | USAGE, CREATE
    object_schema          |
    object_name            | myschema
    object_id              | 45035996274153766
    object_type            | SCHEMA
    grantee_id             | 45035996274153758
    grantee                | user1
    -[ RECORD 3 ]----------+----------------------------------------------------------------------------
    grant_id               | 45035996274153794
    grantor_id             | 45035996273704962
    grantor                | dbadmin
    privileges_description | SELECT, USAGE
    object_schema          |
    object_name            | myschema
    object_id              | 45035996274153766
    object_type            | SCHEMA
    grantee_id             | 45035996274153762
    grantee                | user2
    
    dbadmin=> \c - user1;
    You are now connected as user "user1".
    dbadmin=> CREATE   TABLE  myschema.table1  (id  INT);
    WARNING 6978:  Table "table1" will include privileges from schema "myschema"
    CREATE TABLE
    dbadmin=> \c - user2;
    You are now connected as user "user2".
    dbadmin=> SELECT  *  FROM  myschema.table1;
    (No rows)
    
  • Thanks @Shayon_Sanyal
    I was trying to use 'grant select on all tables', instead of 'grant select on schema'.
    Works like a charm.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file