Обсуждение: Keep a user from creating tables ?
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
On Sun, 16 May 2004, Gregory S. Williamson wrote: > 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.
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:sszabo@megazone.bigpanda.com]
Sent: Mon 5/17/2004 8:03 AM
To: Gregory S. Williamson
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Keep a user from creating tables ?
On Sun, 16 May 2004, Gregory S. Williamson wrote:
> 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.
On Mon, 17 May 2004, Gregory S. Williamson wrote: > 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; 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.
Of course ... implicit in the docs if I reread them. Thanks very much for the tip ... G -----Original Message----- From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com] Sent: Monday, May 17, 2004 1:45 PM To: Gregory S. Williamson Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Keep a user from creating tables ? On Mon, 17 May 2004, Gregory S. Williamson wrote: > 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; 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.