Inherited Privileges

Inherited privileges allow you to grant privileges at the schema level. This enables privileges to be granted automatically to new tables or views in the schema. Existing tables and views are unchanged when you alter the schema to include or exclude inherited privileges. Using inherited privileges eliminates the need to apply the same privileges to each individual table or view in the schema.

To assign inherited privileges, you must be an owner of the schema or a superuser. Assign inherited privileges using the following SQL statements

• GRANT Statements
• CREATE SCHEMA
• ALTER SCHEMA
• CREATE TABLE
• ALTER TABLE
• CREATE VIEW
• ALTER VIEW

Granting Inherited Privileges from One User to Another

The following steps describe a process for user1 to enable inherited privileges to user2.

1. The database user, user1, creates a schema (schema1), and a table (table1) in schema1: 

user1=> CREATE SCHEMA schema1;
user1=> CREATE TABLE schema1.table1 (id int);

2. User user1 grants USAGE and CREATE privileges on schema1 to user2:

user1=> GRANT USAGE ON SCHEMA schema1 to user2;
user1=> GRANT CREATE ON SCHEMA schema1 to user2;

3. The user2 user queries schema1.table1, but the query fails: 

user2=> SELECT * FROM schema1.table1;
ERROR 4367: Permission denied for relation table1

4. The user user1 grants SELECT ON SCHEMA privilege on schema1 to user2: 

user1=> GRANT SELECT ON SCHEMA schema1 to user2;

5. Next, user1 uses ALTER TABLE to include SCHEMA privileges to table1: 

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

6. The user2 query now succeeds:  

user2=> SELECT * FROM schema1.table1;
id
---
(0 rows)

7. User 1 now uses ALTER SCHEMA to include privileges so that all tables created in schema1 inherit schema privileges:

user1=> ALTER SCHEMA schema1 DEFAULT INCLUDE PRIVILEGES;
user1=> CREATE TABLE schema1.table2 (id int);

8. With Inherited Privileges enabled, user2 can query table2 without user1 having to specifically grant privileges on table2:

user2=> SELECT * FROM schema1.table2;
id
---
(0 rows)

Enable or Disable Inherited Privileges at the Database Level

Use the disableinheritedprivileges configuration parameter to enable (0) Inherited Privileges:

=> ALTER DATABASE [database name] SET disableinheritedprivileges = 0;

Use the following configuration parameter to disable (1) Inherited Privileges:

=> ALTER DATABASE [database name] SET disableinheritedprivileges = 1;