Thread: How to prevent users from creating tables

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

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

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

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

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