subject:"\[GENERAL\] Problem revoking a user's 'create' privilege on schema public"

[GENERAL] Problem revoking a user's 'create' privilege on schema public

2008-05-03 Thread jdietrch

I am having trouble revoking a user's create privilege on
schema public.

Here is the sequence of commands that demonstrates the problem:

[EMAIL PROTECTED]:~$ su
Password: 
saturn:/home/jdietrch# su postgres
[EMAIL PROTECTED]:/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
[EMAIL PROTECTED]:/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
[EMAIL PROTECTED]:/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
[EMAIL PROTECTED]:/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
[EMAIL PROTECTED]:/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:
[EMAIL PROTECTED]:~$ uname -a
Linux saturn 2.6.22-3-vserver-k7 #1 SMP Mon Nov 12 11:47:04 UTC 2007
i686 GNU/Linux
[EMAIL PROTECTED]:~$ 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
[EMAIL PROTECTED]:~$ 
-- 
  
  [EMAIL PROTECTED]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2008-05-01 Thread Tom Lane

"James Dietrich" <[EMAIL PROTECTED]> 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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Problem revoking a user's 'create' privilege on schema public

2008-05-01 Thread James Dietrich

I am having trouble revoking a user's create privilege on
schema public.

Here is the sequence of commands that demonstrates the problem:

[EMAIL PROTECTED]:~$ su
Password:
saturn:/home/jdietrch# su postgres
[EMAIL PROTECTED]:/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
[EMAIL PROTECTED]:/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
[EMAIL PROTECTED]:/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
[EMAIL PROTECTED]:/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
[EMAIL PROTECTED]:/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:
[EMAIL PROTECTED]:~$ uname -a
Linux saturn 2.6.22-3-vserver-k7 #1 SMP Mon Nov 12 11:47:04 UTC 2007
i686 GNU/Linux
[EMAIL PROTECTED]:~$ 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
[EMAIL PROTECTED]:~$

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Problem revoking a user's 'create' privilege on schema public

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

[GENERAL] Problem revoking a user's 'create' privilege on schema public

3 matches

Site Navigation

Mail list logo

Footer information