Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question

migrated from stackoverflow.com Nov 29 '12 at 10:36

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
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 K Nov 28 '12 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. – Erwin Brandstetter Nov 28 '12 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. – Aleksey Kontsevich Nov 29 '12 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, for INSERT commands. (Not for UPDATE or DELETE, which don't involve the sequence.) – Erwin Brandstetter Dec 2 '12 at 15:57
Yes, You are right, need to change privileges for sequence too (SELECT, UPDATE), don't work without it. Thanks. :) – Aleksey Kontsevich Dec 4 '12 at 22:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.