Skip to main content

Schema level SELECT on all new tables

  • May 29, 2017
  • 2 replies
  • 24 views
vikasr

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

    2 replies

    Shayon_Sanyal
    Forum|alt.badge.img
    • Shayon_Sanyal
    • Participating Frequently
    • May 29, 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)
    vikasr
    • vikasr
    • Author
    • New Participant
    • May 30, 2017

    Thanks @Shayon_Sanyal
    I was trying to use 'grant select on all tables', instead of 'grant select on schema'.
    Works like a charm.

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement