Обсуждение: revoke on database not working as expected

Hi,

I'm running into a setup problem (I guess) while trying to prevent a
user from creating tables in a database.

The setup is a FreeBSD 5.4 database server accessed from a FreeBSD 6.0
development box, both running PostgreSQL 8.0.3.

This is what I have configured on the database server (firsa):

%%%
[pgsql@firsa] </net/postgresql> tail pg_hba.conf
local   all         @users                            md5
host    all         @users      127.0.0.1/32          md5
host    all         @users      192.168.1.0/24        md5
local   privtest    testpriv                          md5
host    privtest    testpriv    127.0.0.1/32          md5
host    privtest    testpriv    192.168.1.0/24        md5
[pgsql@firsa] </net/postgresql> cat users
stijn
%%%

This is what I do on the dev box (tangaloor):

%%%
[stijn@tangaloor] <~> host tangaloor
tangaloor.lzee.sandcat.nl has address 192.168.1.105
[stijn@tangaloor] <~> psql -U stijn -h firsa template1
Password:
Welcome to psql 8.0.3, the PostgreSQL interactive terminal.
template1=# \du
                       List of users
 User name | User ID |         Attributes         | Groups
-----------+---------+----------------------------+--------
 pgsql     |       1 | superuser, create database |
 stijn     |     100 | superuser, create database |
template1=# create database privtest;
CREATE DATABASE
template1=# create user testpriv password 'password';
CREATE USER
template1=# \c privtest testpriv
Password:
You are now connected to database "privtest" as user "testpriv".
privtest=> create table foo (i varchar(40));
CREATE TABLE
privtest=> \c template1 stijn
Password:
You are now connected to database "template1" as user "stijn".
template1=# revoke all on database privtest from testpriv;
REVOKE
template1=# \c privtest testpriv
Password:
You are now connected to database "privtest" as user "testpriv".
privtest=> create table bar (i varchar(40));
CREATE TABLE
%%%

My question is: why can I still create table 'bar', in a database that
was not created by user 'testpriv', having explicitly revoked all
privileges on that database by a superuser?

What access credential am I missing? I've searched the handbook but all
it says is 'use \z in psql to view privileges' and that doesn't list
general database privileges.

Thanks for any clues. Please CC me as I'm not subscribed.

--Stijn

--
The right half of the brain controls the left half of the body.  This means
that only left handed people are in their right mind.

Stijn Hoop <stijn@win.tue.nl> writes:
> template1=# revoke all on database privtest from testpriv;

That doesn't do what you evidently think it does --- it revokes the
right to create temp tables, and the right to create new schemas, but
not every right in existence.  Please read the GRANT/REVOKE manual
pages.  (Hint: revoking CREATE on the public schema would get you closer
to what you want.)

            regards, tom lane

On Mon, Aug 29, 2005 at 03:07:59PM -0400, Tom Lane wrote:
> Stijn Hoop <stijn@win.tue.nl> writes:
> > template1=# revoke all on database privtest from testpriv;
>
> That doesn't do what you evidently think it does --- it revokes the
> right to create temp tables, and the right to create new schemas, but
> not every right in existence.  Please read the GRANT/REVOKE manual
> pages.  (Hint: revoking CREATE on the public schema would get you closer
> to what you want.)
>
>             regards, tom lane

Thanks for answering!

I assure you that I read those pages. In fact, quoted from:

http://www.postgresql.org/docs/8.0/interactive/sql-grant.html

"Depending on the type of object, the initial default privileges may
include granting some privileges to PUBLIC. The default is no public
access for tables, schemas, and tablespaces;"

This misled me greatly. Apparently this is only for explicitly created
schema's?

Anyway, I found out about psql's \dn+, and that in combination with your hint
was enough:

%%%
You are now connected to database "privtest" as user "stijn".
privtest=# revoke create on schema public from public;
REVOKE
privtest=# \c privtest testpriv
Password:
You are now connected to database "privtest" as user "testpriv".
privtest=> create table plover (i varchar(40));
ERROR:  permission denied for schema public
%%%

which is what I was after. Many thanks!

Might I suggest a hint in this direction somewhere in the text of
REVOKE and GRANT?

--Stijn

--
It's harder to read code than to write it.
        -- Joel Spolsky,
           http://www.joelonsoftware.com/articles/fog0000000069.html