ALTER DEFAULT PERMISSIONS

The ALTER DEFAULT PERMISSIONS page describes the following:

Overview 

The ALTER DEFAULT PERMISSIONS command lets you grant automatic permissions to future objects.

By default, users do not have SELECT permissions on tables created by other users. Database administrators can grant access to other users by modifying the target role default permissions.

For more information about access control, see Access Control.

Permissions 

The SUPERUSER permission is required to alter default permissions.

Syntax 

The following is the syntax for altering default permissions:

alter_default_permissions_statement ::=
      ALTER DEFAULT PERMISSIONS FOR target_role_name
      [IN schema_name, ...]
      FOR { TABLES | SCHEMAS }
      { grant_clause | DROP grant_clause}
      TO ROLE { role_name | public };

grant_clause ::=
   GRANT
      { CREATE FUNCTION
      | SUPERUSER
      | CONNECT
      | CREATE
      | USAGE
      | SELECT
      | INSERT
      | UPDATE
      | DELETE
      | DDL
      | EXECUTE
      | ALL
      }

target_role_name ::= identifier

role_name ::= identifier

schema_name ::= identifier

The following table describes the supported permissions:

Permission	Object	Description
`LOGIN`	Cluster	Login permissions (with a password) allows a role to be a user and login to a database
`PASSWORD`	Cluster	Sets the password for a user role
`CREATE FUNCTION`	Database	Allows a user to create a Python UDF
`SUPERUSER`	Cluster, Database, Schema	The most privileged role, with full control over a cluster, database, or schema
`CONNECT`	Database	Allows a user to connect and use a database
`CREATE`	Database, Schema, Table	For a role to create and manage objects, it needs the `CREATE` and `USAGE` permissions at the respective level
`USAGE`	Schema	For a role to see tables in a schema, it needs the `USAGE` permissions
`SELECT`	Table	Allows a user to run SELECT queries on table contents
`INSERT`	Table	Allows a user to run COPY FROM and INSERT statements to load data into a table
`UPDATE`	Table	Allows a user to modify the value of certain columns in existing rows without creating a table
`DELETE`	Table	Allows a user to run DELETE, TRUNCATE statements to delete data from a table
`DDL`	Database, Schema, Table, Function	Allows a user to alter tables, rename columns and tables, etc.
`EXECUTE`	Function	Allows a user to execute UDFs
`ALL`	Cluster, Database, Schema, Table, Function	All of the above permissions at the respective level

Examples 

This section includes the following examples:

Granting Default Table Permissions 

This example is based on the roles r1 and r2, created as follows:

create role r1;
create role r2;
alter default permissions for r1 for tables grant select to r2;

Once created, you can build and run the following query based on the above:

select
  tdp.database_name as "database_name",
  ss.schema_name as "schema_name",
  rs1.name as "table_creator",
  rs2.name as "grant_to",
  pts.name  as "permission_type"
from sqream_catalog.table_default_permissions tdp
inner join sqream_catalog.roles rs1 on tdp.modifier_role_id = rs1.role_id
inner join sqream_catalog.roles rs2 on tdp.getter_role_id = rs2.role_id
left join sqream_catalog.schemas ss on tdp.schema_id = ss.schema_id
inner join sqream_catalog.permission_types pts on pts.permission_type_id=tdp.permission_type
;

The following is an example of the output generated from the above queries:

database_name	schema_name	table_creator	grant_to	permission_type
master	NULL	public	public	select

For more information about default permissions, see Default Permissions.

Granting Automatic Permissions for Newly Created Schemas 

When the role demo creates a new schema, roles u1,u2 are granted USAGE and CREATE permissions in the new schema, as shown below:

ALTER DEFAULT PERMISSIONS FOR demo FOR SCHEMAS GRANT USAGE, CREATE TO u1,u2;

Granting Automatic Permissions for Newly Created Tables in a Schema 

When the role demo creates a new table in schema s1, roles u1,u2 are granted SELECT permissions, as shown below:

ALTER DEFAULT PERMISSIONS FOR demo IN s1 FOR TABLES GRANT SELECT TO u1,u2;

Revoking Permissions from Newly Created Tables 

Revoking permissions refers to using the DROP GRANT command, as shown below:

ALTER DEFAULT PERMISSIONS FOR public FOR TABLES DROP GRANT SELECT,DDL,INSERT,DELETE TO public;