Postgresql – Unable to create read only user— postgresql 9.4

postgresqlpostgresql-9.4

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.

Best Answer

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;
Related Question