Keep a user from creating tables ?

[Gregory S. Williamson]

In postgres 7.4, is there any way to stop a user from creating tables in a given database ?

I tried (as postgres user):
REVOKE ALL ON DATABASE test FROM testuser;

testuser can still connect and can still select from the tables I want them to see, but as user "testuser":

test=> CREATE TABLE foo(man INT,choo VARCHAR(2));
CREATE TABLE
test=> drop table foo;
DROP TABLE
test=> create temporary table foo (man INT,choo VARCHAR(2));
ERROR: permission denied to create temporary tables in database "test"
test=>

============

I'm sure this is obvious but I'm not seeing it ... not just temporary, but all tables should be disallowed.

TIA,

Greg Williamson
DBA
GlobeXplorer LLC


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Stephan Szabo]

Post by Gregory S. Williamson
In postgres 7.4, is there any way to stop a user from creating tables in a given database ?

Make the user not have rights to create objects in any schemas. Most
likely you'd only need to change the public schema, but if you've granted
create rights for other schemas you'll need to revoke those as well.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Gregory S. Williamson]

Stephan,

Thanks for the info, but alas, no success.

I have:
List of database users
User name | User ID | Attributes
-----------+---------+----------------------------
testuser | 100 |
postgres | 1 | superuser, create database


And as postgres I ran:
REVOKE ALL ON SCHEMA public FROM testuser;

Then I connect:
psql -d testdb -U testuser

(I should not even be able to connect ?)

And I run:
CREATE TABLE foo (man INT, choo(VARCHAR(20));

And it works ...

Am I misundertanding something, or is it simply not possible to prevent users from creating tables ?

In Informix this quite easy:
REVOKE RESOURCE FROM testuser;

I am puzzled at postgres' documentation, and perhaps at its underlying logic.

Any help would be appreciated -- this is sort of important.

Thanks!

Greg W.



-----Original Message-----
From: Stephan Szabo [mailto:***@megazone.bigpanda.com]
Sent: Mon 5/17/2004 8:03 AM
To: Gregory S. Williamson
Cc: pgsql-***@postgresql.org
Subject: Re: [ADMIN] Keep a user from creating tables ?

Post by Gregory S. Williamson
In postgres 7.4, is there any way to stop a user from creating tables in a given database ?

Make the user not have rights to create objects in any schemas. Most
likely you'd only need to change the public schema, but if you've granted
create rights for other schemas you'll need to revoke those as well.




---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Stephan Szabo]

Post by Gregory S. Williamson
Stephan,
Thanks for the info, but alas, no success.
List of database users
User name | User ID | Attributes
-----------+---------+----------------------------
testuser | 100 |
postgres | 1 | superuser, create database
REVOKE ALL ON SCHEMA public FROM testuser;

I forgot to mention something which led to the confusion.
testuser doesn't probably have permissions on the public schema, but
public (all users) does. You probably need to revoke it from PUBLIC
and grant it to the users who should have creation rights to public.


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Gregory S. Williamson]

Of course ... implicit in the docs if I reread them.

Thanks very much for the tip ...
G
-----Original Message-----
From: Stephan Szabo [mailto:***@megazone.bigpanda.com]
Sent: Monday, May 17, 2004 1:45 PM
To: Gregory S. Williamson
Cc: pgsql-***@postgresql.org
Subject: Re: [ADMIN] Keep a user from creating tables ?

Post by Gregory S. Williamson
Stephan,
Thanks for the info, but alas, no success.
List of database users
User name | User ID | Attributes
-----------+---------+----------------------------
testuser | 100 |
postgres | 1 | superuser, create database
REVOKE ALL ON SCHEMA public FROM testuser;

I forgot to mention something which led to the confusion.
testuser doesn't probably have permissions on the public schema, but
public (all users) does. You probably need to revoke it from PUBLIC
and grant it to the users who should have creation rights to public.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org