Re: How to prevent users from creating tables

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