-1

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 to do this ?
Thanks.

Vikas Raturi
  • 916
  • 2
  • 13
  • 23

1 Answers1

1

Figured out the issue from here. Posting here for others.

The only catch in above queries was using "GRANT SELECT ON ALL TABLES IN SCHEMA "
Instead it should be "GRANT SELECT ON SCHEMA"

Vikas Raturi
  • 916
  • 2
  • 13
  • 23