PolarDB:Permissions

SELECT

Allows SELECT from any column, or specific columns, of a table, view, materialized view, or other table-like object. Also enables:

• COPY TO

• Referencing existing column values in UPDATE or DELETE

• The currval function on sequences

• Reading large objects

INSERT

Allows inserting a new row into a table, view, or similar object. Also enables COPY FROM. When granted on specific columns, only those columns can be assigned values in INSERT — other columns receive default values.

UPDATE

Allows updating any column, or specific columns, of a table, view, or similar object. Also enables:

• nextval and setval on sequences

• Writing or truncating large objects

• SELECT ... FOR UPDATE and SELECT ... FOR SHARE (requires SELECT privilege on at least one column as well)

In practice, any non-trivial UPDATE also requires SELECT privilege, because the query must read column values to identify which rows to update.

DELETE

Allows deleting rows from a table, view, or similar object. In practice, any non-trivial DELETE also requires SELECT privilege, because the query must read column values to identify which rows to delete.

TRUNCATE

Allows TRUNCATE on a table.

REFERENCES

Allows creating a foreign key constraint that references a table, or specific columns of a table.

TRIGGER

Allows creating a trigger on a table, view, or similar object.

CREATE

The effect depends on the object type:

• Database: Allows creating new schemas and publications within the database, and installing trusted extensions.

• Schema: Allows creating new objects within the schema. To rename an existing object, you must own the object and hold CREATE on the containing schema.

• Tablespace: Allows creating tables, indexes, and temporary files within the tablespace, and creating databases that use the tablespace as their default.

Revoking CREATE does not affect existing objects — it only prevents new ones from being created.

CONNECT

Allows connecting to the database. This privilege is checked at connection startup, in addition to any restrictions imposed by pg_hba.conf.

TEMPORARY

Allows creating temporary tables while connected to the database.

EXECUTE

Allows calling a function or procedure, including any operators implemented on top of the function. This is the only privilege type applicable to functions and procedures.

USAGE

The effect depends on the object type:

• Procedural languages: Allows using the language to create functions. This is the only privilege type applicable to procedural languages.

• Schemas: Allows looking up objects within the schema (assuming the objects' own privileges are also met). Revoking this privilege does not prevent users from seeing object names via system catalogs, and existing sessions may have already resolved lookups, so this is not a completely secure way to prevent object access.

• Sequences: Allows the currval and nextval functions.

• Types and domains: Allows using the type or domain when creating tables, functions, and other schema objects. This privilege controls which roles can create dependencies on a type — it does not restrict all uses of the type.

• Foreign data wrappers: Allows creating new servers using the wrapper.

• Foreign servers: Allows creating foreign tables using the server. Grantees may also create, alter, or drop their own user mappings associated with the server.

Example

Suppose user miriam creates mytable and runs:

GRANT SELECT ON mytable TO PUBLIC;
GRANT SELECT, UPDATE, INSERT ON mytable TO admin;
GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;

Running \dp mytable in psql shows:

    => \dp mytable
                                      Access privileges
     Schema |  Name   | Type  |   Access privileges   |   Column privileges   | Policies
    --------+---------+-------+-----------------------+-----------------------+----------
     public | mytable | table | miriam=arwdDxt/miriam+| col1:                +|
            |         |       | =r/miriam            +|   miriam_rw=rw/miriam |
            |         |       | admin=arw/miriam      |                       |
    (1 row)

Reading the output:

• miriam=arwdDxt/miriam — the owner holds all applicable privileges on the table

• =r/miriam — PUBLIC has SELECT, granted by miriam

• admin=arw/miriam — admin has SELECT, UPDATE, and INSERT, granted by miriam

• Column privileges show miriam_rw has SELECT and UPDATE on col1

Empty "Access privileges" column

If the "Access privileges" column is empty for an object, the object has default privileges — its ACL entry in the system catalog is null. Default privileges always include all privileges for the owner, plus any default PUBLIC privileges for the object type (see the table above). The first GRANT or REVOKE on an object instantiates the default privileges explicitly (producing an entry like miriam=arwdDxt/miriam) and then applies the requested change.

Similarly, entries are shown in "Column privileges" only for columns with nondefault privileges. (Note: for this purpose, "default privileges" always means the built-in default privileges for the object's type. An object whose privileges have been affected by an ALTER DEFAULT PRIVILEGES command will always be shown with an explicit privilege entry that includes the effects of the ALTER.)

The owner's implicit grant options are not shown in the access privileges display. A * appears only when grant options have been explicitly granted to someone.