Default Privileges

Associated: RBAC

Context

Role based access control comprises a set of features that allow customers to control the privileges of individual users. Default privileges allow users to configure what privileges an object will have by default when it's first created.

PostgreSQL briefly describes default privilege in their privilege documentation. Default privileges for objects created by specified roles can be altered via the ALTER DEFAULT PRIVILEGES command. In PostgreSQL, default privileges are stored in the pg_default_acl catalog table.

What follows is brief description of default privileges in PostgreSQL, for a full understanding please read the linked documentation.

PostgreSQL has system default privileges that always exist in the system and have no corresponding entries in the pg_default_acl catalog table. Some examples of these are, the object owner is granted all privileges and the PUBLIC pseudo role is granted USAGE privileges on all types.
The ALTER DEFAULT PRIVILEGES command only affects objects created by the roles specified in the command, or the current user if no roles are specified. There is no way to alter the default privileges for all roles.
The ALTER DEFAULT PRIVILEGES command can either grant privileges or revoke privileges.
There is a global variant of ALTER DEFAULT PRIVILEGES that affects the default privileges of all objects of a certain type within the current database.
There is a non-global variant of ALTER DEFAULT PRIVILEGES that affects the default privileges of all objects of a certain type within on or more schemas.
System default privileges can be overriden by a global ALTER DEFAULT PRIVILEGES command, but not by a non-global variant.
If a revoke ALTER DEFAULT PRIVILEGES command matches a grant entry in the pg_default_acl table, then it will update the entry from the table and not add any other entries. If the entry ends up with an empty privilege aclitem, then it is removed from the table.
If a revoke ALTER DEFAULT PRIVILEGES command is a global variant and matches a system default privilege, then it will add an entry in the pg_default_acl table that matches the system default, without the part that was revoked.
If a revoke ALTER DEFAULT PRIVILEGES command does not match either of the previous two scenarios, then it has no affect.
When an object is first created, the acl column for that object is NULL. This indicates that the privileges for this object are equivalent to the system default privileges for that object type. It does not indicate that the privileges are equivalent to the default privileges stored in pg_default_acl. Once the privileges of an object are modified, either through an explicit GRANT/REVOKE or through a configured default privilege in pg_default_acl, then the acl column for that object is filled in.

Goals

Allow users to configure default privileges on objects of different types, in a simple and easy to understand way (preferably simpler the PostgreSQL's model).

Overview

We will build a default privileges framework based off of PostgreSQL, but simplified.

Detailed description

The mz_default_privileges table will store default privileges and have the following columns:

role_id text: The id of the role this default privilege applies to. The PUBLIC role ID will indicate that it should apply to all roles.
schema_id text: The id of the schema this default privilege applies to or NULL.
database_id text: The id of the database this default privilege applies to or NULL.
object_type text: Type of object this default privilege applies to.
grantee text: The id of the role that will be granted the privileges.
privileges text: The privileges being granted to grantee.

If both schema_id and database_id are NULL, then the default privilege applies to all objects of a certain type in all databases and schemas.

mz_default_privileges will be pre-populated with certain system default privileges. Currently, the only one is:

(<public-role-id>, NULL, NULL, 'TYPE', <public-role-id>, 'U'). i.e. PUBLIC is granted USAGE on every type.

Roles cannot be deleted while it is referenced in the mz_default_privilges table.

ALTER DEFAULT PRIVILEGES is a command that will have the following syntax (formatted the same way that PostgreSQL formats SQL syntax in their documentation):

ALTER DEFAULT PRIVILEGES [ FOR <role_specification> ] [ { IN SCHEMA <schema_name> [, ...] | IN DATABASE <database_name> [, ...] } ] <abbreviated_grant_or_revoke>

<role_specification>: FOR { { ROLE | USER } <target_role> [, ...] | ALL ROLES }

<abbreviated_grant_or_revoke>: { <grant> | <revoke> }

<grant>: GRANT { <privilege> [, ...] | ALL [ PRIVILEGES ] } ON <object_type> TO [ GROUP ] <role_name> [, ...]

<revoke>: REVOKE { <privilege> [, ...] | ALL [ PRIVILEGES ] } ON <object_type> FROM [ GROUP ] <role_name> [, ...]

The grant variant will add rows to mz_default_privileges, such that:

role_id is filled in from <role_specification>.
schema_id is filled in from <schema_name>.
oject_type is filled in from <object_type>.
privilege is filled in from <grantee>, <privileges>, and <role_name>.

If a matching row already exists, then the <privileges> will be updated instead of having a new row added.

The revoke variant will update an existing row, if one exists. Otherwise, it will have no affect. If a row ends up with empty <privileges> then it will be removed.

Users must be a superuser to specify the ALL ROLES role specification. Users must be a member of both <target_role> and <role_name>.

When an object is first created materialize will do the following in order:

Grant the owner all privileges on that object.
Grant any privileges that match a privilege in the mz_default_privileges table on that object.

Differences from PostgreSQL

This section will highlight the differences between PostgreSQL default privileges and the proposed default privileges for Materialize.

This proposal has no special system privileges that are hard-coded into the system. Instead, we pre-populate the user default privileges table with some initial default privileges.
This proposal allows defining default privileges for all current and future roles.
This proposal allows defining default privileges for multiple databases at once.
This proposal does not differentiate between global and non-global default privilege revokes.
This proposal has no way to override default owner privileges.

Alternatives

Match PostgreSQL exactly.

Open questions

Should we allow users to modify the default owner privileges on an object?
mz_aclitem might not be the best type for the privilege column, because we won't know the actual grantor until the privilege is applied to some object.

20230602_default_privileges.md 7.0 KB History Raw