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?
1 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 separately.
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.
-
So if I GRANT SELECT, INSERT or UPDATE to a table I'll be able to access its sequence values (at least select or read currval). Will it work like columns which goes with table privileges? Or need to explicitly assign smth like USAGE to use it?– Aleksey KNov 28, 2012 at 22:11
-
@AlekseyK: Have you read the first sentence of my answer and the following quote from the manual? A sequence is just a special table with its own privileges, even when attached to a table column. GRANT privileges explicitly. Nov 28, 2012 at 22:20
-
Thank You, I understand. Actually, I think, I do not need to work with sequences in my task as I only need to query column value with serial type after INSERT or UPDATE and I have such access if I have access to the table. So assigning a privilege to a table only will be enough for me. Nov 29, 2012 at 12:51
-
@AlekseyK: On creation, the
serial
type implements an attached sequence with matching privileges. If you later change the privileges of the table, you may have to change privileges of the sequence, too, forINSERT
commands. (Not forUPDATE
orDELETE
, which don't involve the sequence.) Dec 2, 2012 at 15:57 -
Yes, You are right, need to change privileges for sequence too (SELECT, UPDATE), don't work without it. Thanks. :) Dec 4, 2012 at 22:44