Give particular privileges for a table to existing role

Question

My database have some tables such as:

Table A
Table B
Table C

I'm using user monitoring with read-only permission.

SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_name= 'Table A'

I want to set monitoring user with insert, update, delete, truncate, alter table, trigger permission for only table Table A. The another table don't affect. How to implement this?

Answer 1

Use GRANT.

GRANT INSERT, UPDATE, DELETE, TRUNCATE, TRIGGER ON table A TO monitoring;

The role needs privileges for the DB and the schema, too, but since it already has the SELECT privilege, that should be the case. And if you want to keep SELECT and REFERENCES in the mix, you can just:

GRANT ALL ON table A TO monitoring;

But only superuser or the owner of a table can ALTER TABLE. The manual:

You must own the table to use ALTER TABLE.

You would have to make the role monitoring the owner. (Do you really want that??)

ALTER TABLE A OWNER TO monitoring;

Related:

• How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?