pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
select only user
5+ messages / 2 participants
[nested] [flat]

* select only user
@ 2008-01-17 14:30  Jessica Richard <rjessil@yahoo.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Jessica Richard @ 2008-01-17 14:30 UTC (permalink / raw)
  To: pgsql-admin

I created a user and granted only "select" to this user so that  this user can only select the tables on a production system. But by default, this user can also CREATE TABLE successfully....

How can I revoke this "create table" privilege (and possible others) and limit this user to only the permissions granted by me?

Thanks,
Jessica

       
---------------------------------
Be a better friend, newshound, and know-it-all with Yahoo! Mobile.  Try it now.

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: select only user
@ 2008-01-17 15:15  Milen A. Radev <milen@radev.net>
  parent: Jessica Richard <rjessil@yahoo.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Milen A. Radev @ 2008-01-17 15:15 UTC (permalink / raw)
  To: pgsql-admin

Jessica Richard написа:
> I created a user and granted only "select" to this user so that  this user can only select the tables on a production system. But by default, this user can also CREATE TABLE successfully....
> 
> How can I revoke this "create table" privilege (and possible others) and limit this user to only the permissions granted by me?
> 

Revoke the "create" privilege on the "public" schema (or any other 
schema you've created) from 'public' (that's special "role"). Also 
revoke the "create" privilege on the database(s) in question from the 
same special role 'public' to prevent creation of new schemas.

More here - http://www.postgresql.org/docs/current/static/sql-grant.html.

-- 
Milen A. Radev




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: select only user
@ 2008-01-17 15:24  Milen A. Radev <milen@radev.net>
  parent: Milen A. Radev <milen@radev.net>
  0 siblings, 1 reply; 5+ messages in thread

From: Milen A. Radev @ 2008-01-17 15:24 UTC (permalink / raw)
  To: pgsql-admin

Milen A. Radev написа:
> Jessica Richard написа:
>> I created a user and granted only "select" to this user so that  this 
>> user can only select the tables on a production system. But by 
>> default, this user can also CREATE TABLE successfully....
>>
>> How can I revoke this "create table" privilege (and possible others) 
>> and limit this user to only the permissions granted by me?
>>
> 
> Revoke the "create" privilege on the "public" schema (or any other 
> schema you've created) from 'public' (that's special "role"). Also 
> revoke the "create" privilege on the database(s) in question from the 
> same special role 'public' to prevent creation of new schemas.
> 
> More here - http://www.postgresql.org/docs/current/static/sql-grant.html.

Almost forgot - there is another way if the role in question would be 
read-only:

ALTER USER username SET default_transaction_read_only to true;

(http://archives.postgresql.org/pgsql-admin/2007-10/msg00101.php)




-- 
Milen A. Radev




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: select only user
@ 2008-01-18 13:46  Jessica Richard <rjessil@yahoo.com>
  parent: Milen A. Radev <milen@radev.net>
  0 siblings, 1 reply; 5+ messages in thread

From: Jessica Richard @ 2008-01-18 13:46 UTC (permalink / raw)
  To: Milen A. Radev <milen@radev.net>; pgsql-admin

Thanks!!!
"ALTER USER username SET default_transaction_read_only to true" worked. 

One more question:

where do I see the current setting of "default_transacton_read_only (true for false) for each user on the system? Is there a table that keeps this kind of info for all users?

Thanks again,
Jessica

"Milen A. Radev" <milen@radev.net> wrote: Milen A. Radev написа:
> Jessica Richard написа:
>> I created a user and granted only "select" to this user so that  this 
>> user can only select the tables on a production system. But by 
>> default, this user can also CREATE TABLE successfully....
>>
>> How can I revoke this "create table" privilege (and possible others) 
>> and limit this user to only the permissions granted by me?
>>
> 
> Revoke the "create" privilege on the "public" schema (or any other 
> schema you've created) from 'public' (that's special "role"). Also 
> revoke the "create" privilege on the database(s) in question from the 
> same special role 'public' to prevent creation of new schemas.
> 
> More here - http://www.postgresql.org/docs/current/static/sql-grant.html.

Almost forgot - there is another way if the role in question would be 
read-only:

ALTER USER username SET default_transaction_read_only to true;

(http://archives.postgresql.org/pgsql-admin/2007-10/msg00101.php)




-- 
Milen A. Radev


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate


       
---------------------------------
Looking for last minute shopping deals?  Find them fast with Yahoo! Search.

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: select only user
@ 2008-01-18 13:54  Milen A. Radev <milen@radev.net>
  parent: Jessica Richard <rjessil@yahoo.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Milen A. Radev @ 2008-01-18 13:54 UTC (permalink / raw)
  To: pgsql-admin

Jessica Richard написа:
> Thanks!!!
> "ALTER USER username SET default_transaction_read_only to true" worked. 
> 
> One more question:
> 
> where do I see the current setting of "default_transacton_read_only (true for false) for each user on the system? Is there a table that keeps this kind of info for all users?
[...]


SELECT * FROM pg_roles where rolname = '<username>';


-[ RECORD 1 ]-+-------------------------------------
rolname       | <username>
rolsuper      | f
rolinherit    | t
rolcreaterole | f
rolcreatedb   | f
rolcatupdate  | f
rolcanlogin   | t
rolconnlimit  | -1
rolpassword   | ********
rolvaliduntil |
rolconfig     | {default_transaction_read_only=true}
oid           | 53531114


(http://www.postgresql.org/docs/current/static/database-roles.html)



-- 
Milen A. Radev




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2008-01-18 13:54 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-17 14:30 select only user Jessica Richard <rjessil@yahoo.com>
2008-01-17 15:15 ` Milen A. Radev <milen@radev.net>
2008-01-17 15:24   ` Milen A. Radev <milen@radev.net>
2008-01-18 13:46     ` Jessica Richard <rjessil@yahoo.com>
2008-01-18 13:54       ` Milen A. Radev <milen@radev.net>

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