Re: Privileges on public schema can't be revoked? - Mailing list pgsql-general

Greg Fodor <gfodor@gmail.com> writes:
> Apologies in advance about this since it is likely something obvious,
> but I am seeing some very basic behavior that does not make sense.
> I've tested this on a fresh build of 9.6rc1 and also 9.1.24 (just to
> see if it was a regression.) After creating a test database, and a
> test user that I revoke all privileges on the public schema to, yet
> that user is still able to create tables in the public schema.

You would need to revoke the default grant of privileges to PUBLIC;
revokes against any particular user have no effect on his being
a member of PUBLIC.

IOW, revoke only revokes a previous matching grant, and there was
no such grant in this case.  What there was was a grant to PUBLIC;
see the relevant bit in initdb.c:

        "GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;\n\n",

            regards, tom lane