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 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;