Re: Ability to create tables

Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> writes:
> Even though I revoked the  CREATE priv on role ABCREADONLY, it's still able
> to create tables.  What can I do to prevent this?

> $ psql -c 'revoke create on database "ABC123" from "ABCREADONLY";'

That revokes the ability to create new schemas within that database
(which I suspect the role did not have anyway). What you need is
to remove its ability to create objects within the public schema
within that database. By default, that ability is granted to PUBLIC,
so that "revoke create on schema public from "ABCREADONLY";" won't
help either. What you have to do is "revoke create on schema public
from public", and then grant it back to just the roles that should have
it.

If you don't want the role creating temp tables either, you need to
revoke its TEMP right on the database (which *is* a database-level
privilege). Again, this'll involve disallowing that to PUBLIC,
since that default grant is how it's getting the privilege.

regards, tom lane