Thread: Problem revoking a user's 'create' privilege on schema public

Problem revoking a user's 'create' privilege on schema public

From
"James Dietrich"
Date:
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:~$

Re: Problem revoking a user's 'create' privilege on schema public

From
Tom Lane
Date:
"James Dietrich" <jdietrch@gmail.com> writes:
> Why does user2 still have create privilege on schema public?

You revoked that privilege in database postgres, which has little to
do with its state in any other database (and certainly not in template1
which is what you cloned to make db1).

            regards, tom lane