ALTER DEFAULT PERMISSIONS¶

ALTER DEFAULT PERMISSIONS allows granting automatic permissions to future objects.

By default, if one user creates a table, another user will not have SELECT permissions on it. By modifying the target role’s default permissions, a database administrator can ensure that all objects created by that role will be accessible to others.

Learn more about the permission system in the access control guide.

Permissions¶

To alter default permissions, the current role must have the SUPERUSER permission.

Syntax¶

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
      | DELETE
      | DDL
      | EXECUTE
      | ALL
      }

target_role_name ::= identifier

role_name ::= identifier

schema_name ::= identifier

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
`DELETE`	Table	Allows a user to run DELETE, TRUNCATE_IF_EXISTS 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¶

Automatic permissions for newly created schemas¶

When role demo creates a new schema, roles u1,u2 will get USAGE and CREATE permissions in the new schema:

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

Automatic permissions for newly created tables in a schema¶

When role demo creates a new table in schema s1, roles u1,u2 wil be granted with SELECT on it:

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

Revoke (`DROP GRANT`) permissions for newly created tables¶

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