GRANT privileges to table elements

Posted on

Question :

What happens if I GRANT priveleges (for example SELECT) ON a table TO some group role? What privileges will the table’s elements have in this case: indexes, sequences, columns? Should I explicitly GRANT privileges to them if I REVOKE ALL ON SCHEMA public FROM GROUP role and FROM PUBLIC previously?

Answer :

Sequences are separate objects with separate privileges.

Granting permission on a table does not automatically extend
permissions to any sequences used by the table, including sequences
tied to SERIAL columns. Permissions on sequences must be set

Indexes belong to the table. To DROP or CREATE an index you must be the owner of the table (or superuser).

Columns go with table privileges. Granting privileges on the table grants applicable privileges to all columns automatically.

For DDL commands (ALTER TABLE …) you must own the table again.

Since PostgreSQL 9.1 there are column level DML-grants additionally. But:

A user may perform SELECT, INSERT, etc. on a column if he holds that
privilege for either the specific column or its whole table. Granting
the privilege at the table level and then revoking it for one column
will not do what you might wish: the table-level grant is unaffected
by a column-level operation.

More details in the manual about GRANT, where the quotes are from.

Leave a Reply

Your email address will not be published. Required fields are marked *