Example usage: implementing inherited privileges

The following steps show how user Joe enables inheritance of privileges on a given schema so other users can access tables in that schema.

1. Joe creates schema schema1, and creates table table1 in it:

   
   =>\c - Joe
   You are now connected as user Joe
   => CREATE SCHEMA schema1;
   CRDEATE SCHEMA
   => CREATE TABLE schema1.table1 (id int);
   CREATE TABLE
   

2. Joe grants USAGE and CREATE privileges on schema1 to Myra:

   
   => GRANT USAGE, CREATE ON SCHEMA schema1 to Myra;
   GRANT PRIVILEGE
   

3. Myra queries schema1.table1, but the query fails:

   
   =>\c - Myra
   You are now connected as user Myra
   => SELECT * FROM schema1.table1;
   ERROR 4367: Permission denied for relation table1
   

4. Joe grants Myra SELECT ON SCHEMA privileges on schema1:

   
   =>\c - Joe
   You are now connected as user Joe
   => GRANT SELECT ON SCHEMA schema1 to Myra;
   GRANT PRIVILEGE
   

5. Joe uses ALTER TABLE to include SCHEMA privileges for table1:

   
   => ALTER TABLE schema1.table1 INCLUDE SCHEMA PRIVILEGES;
   ALTER TABLE
   

6. Myra's query now succeeds:

   
   =>\c - Myra
   You are now connected as user Myra
   => SELECT * FROM schema1.table1;
   id
   ---
   (0 rows)
   

7. Joe modifies schema1 to include privileges so all tables created in schema1 inherit schema privileges:

   
   =>\c - Joe
   You are now connected as user Joe
   => ALTER SCHEMA schema1 DEFAULT INCLUDE PRIVILEGES;
   ALTER SCHEMA
   => CREATE TABLE schema1.table2 (id int);
   CREATE TABLE
   

8. With inherited privileges enabled, Myra can query table2 without Joe having to explicitly grant privileges on the table:

   
   =>\c - Myra
   You are now connected as user Myra
   => SELECT * FROM schema1.table2;
   id
   ---
   (0 rows)