pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Keep 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