On 03/09/2018 05:46 PM, Tom Lane wrote:
Ron Johnson <ron.l.johnson@xxxxxxx> 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.
Thanks.
--
Angular momentum makes the world go 'round.