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.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
