revoke on database not working as expected - Mailing list pgsql-general

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.