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 |
---|---|---|
|
Cluster |
Login permissions (with a password) allows a role to be a user and login to a database |
|
Cluster |
Sets the password for a user role |
|
Database |
Allows a user to create a Python UDF |
|
Cluster, Database, Schema |
The most privileged role, with full control over a cluster, database, or schema |
|
Database |
Allows a user to connect and use a database |
|
Database, Schema, Table |
For a role to create and manage objects, it needs the |
|
Schema |
For a role to see tables in a schema, it needs the |
|
Table |
Allows a user to run SELECT queries on table contents |
|
Table |
Allows a user to run COPY FROM and INSERT statements to load data into a table |
|
Table |
Allows a user to run DELETE, TRUNCATE_IF_EXISTS statements to delete data from a table |
|
Database, Schema, Table, Function |
Allows a user to alter tables, rename columns and tables, etc. |
|
Function |
Allows a user to execute UDFs |
|
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;