Thread

1. Ability to create tables

   Ron Johnson <ron.l.johnson@cox.net> — 2018-03-09T23:13:48Z

   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.
   
   
   

2. Re: Ability to create tables

   Tom Lane <tgl@sss.pgh.pa.us> — 2018-03-09T23:46:15Z

   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
   
   
   

3. Re: Ability to create tables

   Ron Johnson <ron.l.johnson@cox.net> — 2018-03-10T00:10:47Z

   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.