Revoke Create on public Schema from user - is not working in Postgresql

Question

I am using postgresql -8.4.20 which is a back-end for JON server and login with username rhqadmin and connected to database rhq.

After connecting It is redirecting me to public schema by default. Now I don't want to allow this user rhqadmin to create tables in public Schema.

So I am revoking the Create privilege on public schema by using following command:-

Revoke Create ON SCHEMA public FROM rhqadmin;

Then it is giving me warning -

"no privileges could be revoked for "public".

When I login with super user postgres and from there I revoke 'CREATE' privilege on public Schema for user rhqadmin then it is not giving any warning and successfully revoked then I logged in with user rhqadmin and try to create table in public schema and again it is allowing me to Create table even after revoking the create privilege from super user.

Please tell me why revoke privilege is not working for public Schema or I am doing any mistake.

Regards,
Avinash

Answer 1

Your problem is that the CREATE privilege is not granted to rhqadmin, but to the special role PUBLIC to which everybody belongs by default.

Since privileges in PostgreSQL are additive, there is no way to explicitly forbid something to only a single user if it is allowed to everybody.

The solution is probably:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;