pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedKeep a user from creating tables ?
5+ messages / 2 participants
[nested] [flat]
* Keep a user from creating tables ?
@ 2004-05-17 04:02 Gregory S. Williamson <gsw@globexplorer.com>
0 siblings, 1 reply; 5+ messages in thread
From: Gregory S. Williamson @ 2004-05-17 04:02 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Keep a user from creating tables ?
@ 2004-05-17 15:03 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Gregory S. Williamson <gsw@globexplorer.com>
0 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2004-05-17 15:03 UTC (permalink / raw)
To: Gregory S. Williamson <gsw@globexplorer.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Keep a user from creating tables ?
@ 2004-05-17 19:38 Gregory S. Williamson <gsw@globexplorer.com>
0 siblings, 1 reply; 5+ messages in thread
From: Gregory S. Williamson @ 2004-05-17 19:38 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Keep a user from creating tables ?
@ 2004-05-17 20:45 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Gregory S. Williamson <gsw@globexplorer.com>
0 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2004-05-17 20:45 UTC (permalink / raw)
To: Gregory S. Williamson <gsw@globexplorer.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Keep a user from creating tables ?
@ 2004-05-17 21:53 Gregory S. Williamson <gsw@globexplorer.com>
0 siblings, 0 replies; 5+ messages in thread
From: Gregory S. Williamson @ 2004-05-17 21:53 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-05-17 21:53 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-17 04:02 Keep a user from creating tables ? Gregory S. Williamson <gsw@globexplorer.com>
2004-05-17 15:03 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-05-17 19:38 Re: Keep a user from creating tables ? Gregory S. Williamson <gsw@globexplorer.com>
2004-05-17 20:45 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-05-17 21:53 Re: Keep a user from creating tables ? Gregory S. Williamson <gsw@globexplorer.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox