GRANT PRIVILEGE

GRANT grants privileges on a database object. The PUBLIC pseudo-role can be used to indicate that the privileges should be granted to all roles (including roles that might not exist yet).

Privileges are cumulative: revoking a privilege from PUBLIC does not mean all roles have lost that privilege, if certain roles were explicitly granted that privilege.

ALPHA! This feature is in alpha. It has known performance or stability issues and is under active development. It is not subject to our backwards compatibility guarantees.

You must contact us to enable this feature in your Materialize region.

Syntax

GRANT privilege , ALL PRIVILEGES ON TABLE TYPE SECRET CONNECTION DATABASE SCHEMA CLUSTER object_name TO GROUP role_name ,

privilege

SELECT INSERT UPDATE DELETE CREATE USAGE
Field Use
object_name The object that privileges are being granted on.
role_name The role name that is gaining privileges. Use the PUBLIC pseudo-role to grant privileges to all roles.
SELECT Allows reading rows from an object. The abbreviation for this privilege is ‘r’ (read).
INSERT Allows inserting into an object. The abbreviation for this privilege is ‘a’ (append).
UPDATE Allows updating an object (requires SELECT if a read is necessary). The abbreviation for this privilege is ‘w’ (write).
DELETE Allows deleting from an object (requires SELECT if a read is necessary). The abbreviation for this privilege is ’d'.
CREATE Allows creating a new object within another object. The abbreviation for this privilege is ‘C’.
USAGE Allows using an object or looking up members of an object. The abbreviation for this privilege is ‘U’.
ALL All applicable privileges for the provided object type.

Details

The following table describes which privileges are applicable to which objects:

Object type All privileges
DATABASE UC
SCHEMA UC
TABLE arwd
(VIEW) r
(MATERIALIZED VIEW) r
INDEX
TYPE U
(SOURCE) r
SINK
CONNECTION U
SECRET U
CLUSTER UC

Unlike PostgreSQL, UPDATE and DELETE always require SELECT privileges on the object being updated.

Compatibility

For PostgreSQL compatibility reasons, you must specify TABLE as the object type for sources, views, and materialized views, or omit the object type.

Examples

GRANT SELECT ON mv TO joe, mike;
GRANT USAGE, CREATE ON DATABASE materialize TO joe;
GRANT ALL ON CLUSTER dev TO joe;
Back to top ↑