Thread: Ability to create tables

Hi,

Archaic v9.2.7

Even though I revoked the  CREATE priv on role ABCREADONLY, it's still able 
to create tables.  What can I do to prevent this?

\c postgres
CREATE ROLE "ABCREADONLY" LOGIN INHERIT PASSWORD 'Flying.Fox';
GRANT CONNECT ON DATABASE "ABC123" TO "ABCREADONLY";

\c ABC123
GRANT USAGE ON SCHEMA public, ABC, tms TO "ABCREADONLY";
GRANT SELECT ON ALL TABLES IN SCHEMA public, ABC, tms TO "ABCREADONLY";
ALTER DEFAULT PRIVILEGES IN SCHEMA public, ABC, tms GRANT SELECT ON TABLES 
TO "ABCREADONLY";

$ psql -c 'revoke create on database "ABC123" from "ABCREADONLY";'
REVOKE
$ psql -d ABC123 -U ABCREADONLY -c "create table bar (f1 integer);"
CREATE TABLE
$ psql -d ABC123 -U ABCREADONLY -c "\d bar"
       Table "public.bar"
Column |  Type   | Modifiers
--------+---------+-----------
f1     | integer |

Thanks

-- 
Angular momentum makes the world go 'round.

Ron Johnson <ron.l.johnson@cox.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

On 03/09/2018 05:46 PM, Tom Lane wrote:
> Ron Johnson <ron.l.johnson@cox.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.

Thanks.


-- 
Angular momentum makes the world go 'round.