Date: Thu, 5 Feb 2009 01:37:41 +1100
From: raf <raf@raf.org>
To: pgsql-admin@postgresql.org
Subject: Re: How to prevent users from creating tables
Message-ID: <20090204143740.GA25312@raf.org>
Mail-Followup-To: pgsql-admin@postgresql.org
References: <20090204005222.GA19588@raf.org> <gmat64$unq$1@ger.gmane.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <gmat64$unq$1@ger.gmane.org>

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