I am having trouble revoking a user's create privilege on
schema public.
Here is the sequence of commands that demonstrates the problem:
jdietrch@saturn:~$ su
Password:
saturn:/home/jdietrch# su postgres
postgres@saturn:/home/jdietrch$ psql
Welcome to psql 8.3.1, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=# revoke all on schema public from public;
REVOKE
postgres=# create role user1 password 'user1' login createdb;
CREATE ROLE
postgres=# create role user2 password 'user2' login;
CREATE ROLE
postgres=# revoke all on schema public from user2;
REVOKE
postgres=# grant usage on schema public to user2;
GRANT
postgres=# \q
postgres@saturn:/home/jdietrch$ psql -U user1 template1
Password for user user1:
Welcome to psql 8.3.1, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
template1=> create database db1;
CREATE DATABASE
template1=> \q
postgres@saturn:/home/jdietrch$ psql -U user1 db1
Password for user user1:
Welcome to psql 8.3.1, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
db1=> create table table1(id integer);
CREATE TABLE
db1=> select has_schema_privilege('public', 'create');
has_schema_privilege
----------------------
t
(1 row)
db1=> \q
postgres@saturn:/home/jdietrch$ psql -U user2 db1
Password for user user2:
Welcome to psql 8.3.1, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
db1=> create table table2(id integer);
CREATE TABLE
db1=> select has_schema_privilege('public', 'create');
has_schema_privilege
----------------------
t
(1 row)
db1=> \q
postgres@saturn:/home/jdietrch$
Notice that both user1 and user2 were allowed to create
a table in the database.
Why does user2 still have create privilege on schema public?
I am expecting that user2 should not be permitted to
create a table in the database that user1 created.
If someone could point out to me what I'm doing wrong,
I'd be very grateful.
Thank you,
James Dietrich
P.S. I'm running Debian GNU/Linux:
jdietrch@saturn:~$ uname -a
Linux saturn 2.6.22-3-vserver-k7 #1 SMP Mon Nov 12 11:47:04 UTC 2007
i686 GNU/Linux
jdietrch@saturn:~$ psql -U user1 template1
Password for user user1:
Welcome to psql 8.3.1, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
template1=> select version();
version
----------------------------------------------------------------------------------------
PostgreSQL 8.3.1 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3
(Debian 4.2.3-2)
(1 row)
template1=> \q
jdietrch@saturn:~$
