Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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:~$ 
-- 
  
  jdietrch@xxxxxxxxxxx

-- 
http://www.fastmail.fm - IMAP accessible web-mail



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux