0

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'

enter image description here

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?

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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