Skip site navigation (1) Skip section navigation (2)

Re: Postgres roles

From: Shane Ambler <pgsql(at)Sheeky(dot)Biz>
To: ptufenkji(at)usj(dot)edu(dot)lb
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Postgres roles
Date: 2008-02-08 13:53:50
Message-ID: 47AC5EEE.1070304@Sheeky.Biz (view raw or flat)
Thread:
Lists: pgsql-sql
Pascal Tufenkji wrote:

> My questions are:
> 
>  
> 
> 1.      how do I identify the users assigned to this role :
> (in the older version)
> SELECT grolist from pg_group where groname = 'sti'; 
> 

"The view pg_group exists for backwards compatibility: it emulates a 
catalog that existed in PostgreSQL before version 8.1. It shows the 
names and members of all roles that are marked as not rolcanlogin, which 
is an approximation to the set of roles that are being used as groups."


Use pg_roles to get the user and group info. Use pg_auth_members to get 
the list of members that belong to each group role.
(any role can be used for a group but it is usually a role that has 
rolcanlogin set to false, and has members recorded in pg_auth_members)

http://www.postgresql.org/docs/8.2/interactive/user-manag.html
can explain it better - or more specifically
http://www.postgresql.org/docs/8.2/interactive/role-membership.html

> 
> 2.      how do I differ granting permissions on a table to the user sti from
> the whole members of the group sti
> (in the older version)
> GRANT SELECT ON table TO group sti;
> GRANT SELECT,INSERT,UPDATE,DELETE ON table TO sti;
> 

Use a more descriptive name for the group or simply sti_group.


I am guessing that you have an issue because you now have one role 
called sti - that has carried the group members from the old version - 
this is the admin userid used to login but because it is used as a group 
it passes it's privileges to all members of sti.





-- 

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

In response to

Responses

pgsql-sql by date

Next:From: Pascal TufenkjiDate: 2008-02-08 14:54:07
Subject: Re: Postgres roles
Previous:From: Shane AmblerDate: 2008-02-08 13:17:51
Subject: Re: Serial not nulla

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group