Thread: How to prevent users from creating tables

How to prevent users from creating tables

From
raf
Date:
hi,

in my database, normal users can't select, insert, update or
delete anything directly. they can only call functions which
are all security defining functions which give access to the
data. that's the way i like it.

however, i discovered that normal users can create new
tables in the database. is there any way to prevent this?
i couldn't find any grant/revoke syntax in the doco that
looked relevant.

cheers,
raf


Re: How to prevent users from creating tables

From
"Milen A. Radev"
Date:
raf написа:
> hi,
>
> in my database, normal users can't select, insert, update or
> delete anything directly. they can only call functions which
> are all security defining functions which give access to the
> data. that's the way i like it.
>
> however, i discovered that normal users can create new
> tables in the database. is there any way to prevent this?
> i couldn't find any grant/revoke syntax in the doco that
> looked relevant.
[...]

You need to revoke the "CREATE" privilege for the schema(s) in question
(http://www.postgresql.org/docs/current/static/sql-grant.html#SQL-GRANT-DESCRIPTION-OBJECTS).


--
Milen A. Radev

Re: How to prevent users from creating tables

From
raf
Date:
Milen A. Radev wrote:

> raf ????????????:
> > hi,
> >
> > in my database, normal users can't select, insert, update or
> > delete anything directly. they can only call functions which
> > are all security defining functions which give access to the
> > data. that's the way i like it.
> >
> > however, i discovered that normal users can create new
> > tables in the database. is there any way to prevent this?
> > i couldn't find any grant/revoke syntax in the doco that
> > looked relevant.
> [...]
>
> You need to revoke the "CREATE" privilege for the schema(s) in question
> (http://www.postgresql.org/docs/current/static/sql-grant.html#SQL-GRANT-DESCRIPTION-OBJECTS).
>
> --
> Milen A. Radev

i had revoked all privileges on the database.
i didn't realise this privilege applied to schemas.
so, i'll revoke this privilege from the public schema.

hmm, if i try:

  revoke all privileges on schema public from public;
  revoke all privileges on schema public from staff; -- staff is a role

then i get two warnings:

  WARNING:  no privileges could be revoked for "public"
  WARNING:  no privileges could be revoked for "public"

and a member of the staff role can still create tables.
if i replace "all privileges" with just "create", i
get the same warnings and still no effect.

what did i do wrong?

PostgreSQL 8.3.5 on i386-apple-darwin8.11.1,
compiled by GCC i686-apple-darwin8-gcc-4.0.1
(GCC) 4.0.1 (Apple Computer, Inc. build 5370)


many thanks,
raf


Re: How to prevent users from creating tables

From
"Kevin Grittner"
Date:
>>> raf <raf@raf.org> wrote:
>   revoke all privileges on schema public from public;
>   revoke all privileges on schema public from staff; -- staff is a
role
>
> then i get two warnings:
>
>   WARNING:  no privileges could be revoked for "public"
>   WARNING:  no privileges could be revoked for "public"
>
> and a member of the staff role can still create tables.
> if i replace "all privileges" with just "create", i
> get the same warnings and still no effect.

Are you logged in as a database superuser?

(Database owner is not good enough for this operation.)

-Kevin

Re: How to prevent users from creating tables

From
raf
Date:
Kevin Grittner wrote:

> >>> raf <raf@raf.org> wrote:
> >   revoke all privileges on schema public from public;
> >   revoke all privileges on schema public from staff; -- staff is a role
> >
> > then i get two warnings:
> >
> >   WARNING:  no privileges could be revoked for "public"
> >   WARNING:  no privileges could be revoked for "public"
> >
> > and a member of the staff role can still create tables.
> > if i replace "all privileges" with just "create", i
> > get the same warnings and still no effect.
>
> Are you logged in as a database superuser?
>
> (Database owner is not good enough for this operation.)
>
> -Kevin

i wasn't logged in as the superuser.
thanks. it's good now.

cheers,
raf