Horton

Thread

  1. Revoking CREATE TABLE

    Tony Webb <amw@sanger.ac.uk> — 2010-03-24T14:51:09Z

    Hi,
 
I would like a setup with the following:
 
Three users - one, called OWNER, that owns the tables and can drop,
alter and change data in the tables; another called USER that can edit
data in the tables created by the owner but cannot create new tables or
drop any tables and a third user called RO which has read only access to
OWNER's tables but cannot change any data or create new tables etc.
 
I can grant table privileges to the USER and RO but how do I stop these
two users from creating  new tables etc?
 
I'm creating the tables from a script so I don't think I can easily
create a schema so am relying on the public schema.
 
I suspect I'm missing something fundamental here :-\
 
All help gratefully received.
 
Thanks
 
Pif



-- 
 The Wellcome Trust Sanger Institute is operated by Genome Research 
 Limited, a charity registered in England with number 1021457 and a 
 company registered in England with number 2742969, whose registered 
 office is 215 Euston Road, London, NW1 2BE.

  2. Re: Revoking CREATE TABLE

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-03-24T15:23:51Z

    "Tony Webb" <amw@sanger.ac.uk> writes:
> I can grant table privileges to the USER and RO but how do I stop these
> two users from creating  new tables etc?

Revoke CREATE privilege on the public schema from PUBLIC (and then grant
it back to OWNER and whoever else you want to have it).

If you don't want them creating temp tables either, similarly revoke
the database-level TEMP privilege.

			regards, tom lane

  3. Re: Revoking CREATE TABLE

    Tony Webb <amw@sanger.ac.uk> — 2010-03-24T16:28:37Z

    Thanks Tom,

I think I'm still doing something wrong.

As a superuser I run:
#revoke create on schema public from public;
REVOKE

As the read only user straight after running the above:

create table barney2(col1 integer);
CREATE TABLE
\d barney2
    Table "public.barney2"
 Column |  Type   | Modifiers
--------+---------+-----------
 col1   | integer |

What should I try next? Presumably the privilege is being inherited from
another role?

Cheers

Pif



-- 
 The Wellcome Trust Sanger Institute is operated by Genome Research 
 Limited, a charity registered in England with number 1021457 and a 
 company registered in England with number 2742969, whose registered 
 office is 215 Euston Road, London, NW1 2BE.

  4. Re: Revoking CREATE TABLE

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-03-24T16:37:28Z

    "Tony Webb" <amw@sanger.ac.uk> writes:
> Thanks Tom,
> I think I'm still doing something wrong.

> As a superuser I run:
> #revoke create on schema public from public;
> REVOKE

> As the read only user straight after running the above:

> create table barney2(col1 integer);
> CREATE TABLE

It works for me:

regression=# create user ro;
CREATE ROLE
regression=# \c - ro
You are now connected to database "regression" as user "ro".
regression=> create table t1 (f1 int);
CREATE TABLE

[ in another session, as superuser ]

regression=# revoke create on schema public from public;
REVOKE

[ back to first session ]

regression=> create table t2 (f1 int);
ERROR:  permission denied for schema public

Are you sure you revoked the privilege in the same database the read
only user is working in?

> What should I try next? Presumably the privilege is being inherited from
> another role?

Not unless you manually set things up that way.

			regards, tom lane

  5. Re: Revoking CREATE TABLE

    Tony Webb <amw@sanger.ac.uk> — 2010-03-24T17:13:31Z

    Hi Tom,

Thanks for your help and the hint (off-line) to use the \dn+ command.
You've hit the nail on the head sir!

\dn+
WARNING:  nonstandard use of \\ in a string literal at character 281
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
                                                               List of
schemas
        Name        |  Owner   |                            Access
privileges                             |           Description
--------------------+----------+----------------------------------------
----------------------------------+----------------------------------
 information_schema | postgres | {postgres=UC/postgres,=U/postgres}
|
 pg_catalog         | postgres | {postgres=UC/postgres,=U/postgres}
| system catalog schema
 pg_toast           | postgres |
| reserved schema for TOAST tables
 pg_toast_temp_1    | postgres |
|
 public             | postgres |
{postgres=UC/postgres,=U/postgres,gb_ro=UC/postgres,gb_owner=C/postgres}
| standard public schema
(5 rows)

I'd not used that before.

It shows that the gb_ro user also had extra privs granted to it for the
public schema too (unless I'm reading it wrong). I've revoked them
('all' and 'create') and it works fine now!

There should be 2 additional nologin roles on the db - gb_role and
gb_role_ro - all object privs should be via them, gb_ro should not have
its own privileges.

Thanks again, you're a star!

Pif



-- 
 The Wellcome Trust Sanger Institute is operated by Genome Research 
 Limited, a charity registered in England with number 1021457 and a 
 company registered in England with number 2742969, whose registered 
 office is 215 Euston Road, London, NW1 2BE.

  6. Re: Revoking CREATE TABLE

    Adrian von Bidder <avbidder@fortytwo.ch> — 2010-03-25T07:56:51Z

    On Wednesday 24 March 2010 17.28:37 Tony Webb wrote:
> #revoke create on schema public from public;
> REVOKE

Note that this will *only* revoke the create right from "PUBLIC" and not 
from any individual role that may have the right.  PostgreSQL unfortunately 
does not have a "revoke ... from ALL".

So you need to revoke from all individual roles that have the right on this 
database/schema.

cheers
-- vbi

-- 
Vorteil kleiner Menschen:  Sie werden bei Regen später naß.