ALTER DEFAULT PERMISSIONS

ALTER DEFAULT PERMISSIONS allows granting automatic permissions to future objects.

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

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