From: raf <raf@raf.org>
To: pgsql-admin@postgresql.org
Subject: Re: How to prevent users from creating tables
Date: Thu, 5 Feb 2009 01:37:41 +1100
Message-ID: <20090204143740.GA25312@raf.org> (raw)
In-Reply-To: <gmat64$unq$1@ger.gmane.org>
References: <20090204005222.GA19588@raf.org>
<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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: raf@raf.org
Subject: Re: How to prevent users from creating tables
In-Reply-To: <20090204143740.GA25312@raf.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox