1

I am using PostgreSQL 9.4, I want to make a user with permanent read only access on public schema. I have tried many different ways to get this but using

REVOKE ALL on schema public to user1; 
GRANT SELECT on all tables in schema PUBLIC to user1;

doesn't make any sense for me, user user1 is not able to change any table but is still able to create new tables in public schema.

  • Did you commit;? – Colin 't Hart Apr 16 '15 at 13:24
  • 1
    Did you try to explicitly revoke the create privilege? revoke create on schema public from user1. Btw: your example revoke is invalid and won't run because it's revoke ... from ... not revoke .. to ... – a_horse_with_no_name Apr 16 '15 at 13:30
  • Had you used 'commit;' statement after used of GRANT Statement. – Md Haidar Ali Khan Apr 16 '15 at 13:34
1

You're misunderstanding how REVOKE works.

You can't REVOKE a right that was never GRANTed. It doesn't create a deny ACL (a negative right), it removes a granted right if it exist. You didn't GRANT rights to user1 on the schema; instead that user inherited them from the public role everyone's a member of. So you can't revoke it.

You want to revoke the right given to public, then grant it to roles who should have read/write. quick and dirty example:

CREATE ROLE read_write_user;
GRANT read_write_user TO user2;
GRANT read_write_user TO user3;
GRANT ALL ON SCHEMA public TO read_write_user;
REVOKE ALL ON SCHEMA public FROM public;
GRANT USAGE ON SCHEMA public TO user1;

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.