Thread: Revoking CREATE TABLE

Revoking CREATE TABLE

From

"Tony Webb"

Date:

24 March 2010, 15:12:07

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.

Re: Revoking CREATE TABLE

From

Tom Lane

Date:

24 March 2010, 15:24:04

"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

Re: Revoking CREATE TABLE

From

"Tony Webb"

Date:

24 March 2010, 16:28:53

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.

Re: Revoking CREATE TABLE

From

Tom Lane

Date:

24 March 2010, 16:37:40

"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

Re: Revoking CREATE TABLE

From

"Tony Webb"

Date:

24 March 2010, 17:13:46

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.

Re: Revoking CREATE TABLE

From

Adrian von Bidder

Date:

25 March 2010, 07:57:11

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ß.

Attachment

signature.asc