Materialize/doc/developer/design/20230531_system_privileges.md

System Privileges

• Associated: RBAC

Context

Role based access control comprises a set of features that allow customers to control the privileges of individual users. Object privileges control user's access to database objects, they are granted with the GRANT keyword and revoked with the REVOKE keyword. For example, GRANT SELECT ON TABLE t TO r will grant the ability to read table t to role r. GRANT CREATE ON SCHEMA s TO r will grant the ability to create new objects in schema s to role r. Object privileges can be inherited through role membership. Role attributes control the user's ability to create (and sometimes modify) top level objects, they are granted and revoked with the ALTER ROLE keywords. For example, ALTER ROLE r CREATEDB will grant the ability to create databases to role r. ALTER ROLE r CREATECLUSTER will grant the ability to create clusters to role r. Role attributes cannot be inherited through role membership.

Role attributes always exist in a boolean state. To change the state of a role attribute you must alter the role. For example ALTER ROLE joe CREATEDB sets the CREATEDB attribute to true, and ALTER ROLE NOCREATEDB sets the CREATEDB attribute to false.

On the other hand, privileges either exist or they don't exist. You create them by granting a privilege via GRANT ... and you destroy them via REVOKE ....

Role attributes present some problems as they currently exist:

• It can be confusing that there are two separate privilege primitives that control a role's privilege, and they have different semantics.
• Role attributes are not inherited which makes it cumbersome to grant attributes to a large number of roles.
• Users are not able to easily grant the ability to create top level objects to many current and future users in a short amount of time.

Goals

• Reduce confusion around privileges.
• Allow the ability to create databases, clusters, and roles to be inherited through role membership.
• Allow users to easily grant the ability to create top level objects to many current and future users in a short amount of time.

Overview

We will add a new type of privilege called System Privileges. They will look and act just like normal object privileges, but they will grant the abilities that would otherwise be granted through role attributes. These will replace role attributes.

NOTE: Other databases, such as CockroachDB and Snowflake, have some form of system privileges that control similar privileges to role attributes.

Detailed description

System privileges will be granted with the following syntax: GRANT <privilege-specification> ON SYSTEM TO <role-specification>

System privileges will be revoked with the following syntax: REVOKE <privilege-specification> ON SYSTEM FROM <role-specification>

We will support the following system privileges:

• CREATEDB: Allows users to create databases.
• CREATECLUSTER: Allows users to create clusters.
• CREATEROLE: Allows users to create, modify, and delete roles.

System privileges are inherited through role membership.

System privileges will be exposed through a system table called mz_system_privileges. It will have a single column of type mz_aclitem that will store all the privileges.

Role attributes can be added back in at a later point if we determine that they are necessary.

Observability

We will add the following function: has_system_privilege([role: text or oid, ]privileges: text) -> bool to indicate whether a role has a system privilege. The semantics of this function will match our existing access privilege inquiry functions.

Future Work

The overall observability for privileges could be improved by adding SHOW commands that show a user their current privileges. Some examples may be:

• SHOW PRIVILEGES would show all privileges for the current user.
• SHOW PRIVILEGES ON SYSTEM would show all system privileges for the current user.
• SHOW PRIVILEGES ON TABLE t would show all table privileges for the current user on t.
• etc.

While these aren't specific to system privileges, they are relevant.

Alternatives

• Add system privileges and don't remove role attributes.
• Make role attributes inheritable and lose some PostgreSQL compatibility.
• Keep the current setup.

Open questions

None currently.