Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id B514B2E0354 for ; Fri, 8 Feb 2008 10:56:25 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 16489-03 for ; Fri, 8 Feb 2008 10:56:01 -0400 (AST) Received: from Citrus.usj.edu.lb (rectorat2.usj.edu.lb [193.227.187.140]) by postgresql.org (Postfix) with ESMTP id A89542E0333 for ; Fri, 8 Feb 2008 10:56:05 -0400 (AST) Received: from pascolo ([192.168.100.239]) by Citrus.usj.edu.lb (8.13.1/8.13.1) with ESMTP id m18Eg3F1025812; Fri, 8 Feb 2008 16:42:03 +0200 Message-Id: <200802081442.m18Eg3F1025812@Citrus.usj.edu.lb> Reply-To: From: "Pascal Tufenkji" To: "'Shane Ambler'" Cc: Subject: Re: Postgres roles Date: Fri, 8 Feb 2008 16:54:07 +0200 Organization: USJ MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_010D_01C86A73.389BFF70" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198 In-Reply-To: <47AC5EEE.1070304@Sheeky.Biz> Thread-Index: AchqWFlsn5vtStmDSEWTsncJmh91zAACEX0Q X-USJ-MailScanner-Information: Please contact the ISP for more information X-USJ-MailScanner: Found to be clean X-USJ-MailScanner-From: ptufenkji@usj.edu.lb X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=BAYES_50=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200802/63 X-Sequence-Number: 30154 This is a multi-part message in MIME format. ------=_NextPart_000_010D_01C86A73.389BFF70 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi Shane, You are exactly right. My issue is that, I now have one role called sti - that has carried the group members from the old version - So what do you think my options are, so I can separate them? I have only one option in my mind: - Revoke the members from the role sti - Create a new role (that has rolcanlogin set to false) called sti_group - Assign the members to it - Finally, fix all the permissions for all the tables (add the permissions to the new group sti_group) which seems like a huge amount of work. In that case I'll be able to give permissions such as : GRANT SELECT ON table TO sti_group; GRANT SELECT,INSERT,UPDATE,DELETE ON table TO sti; Is there a better solution ? Pascal -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Shane Ambler Sent: Friday, February 08, 2008 3:54 PM To: ptufenkji@usj.edu.lb Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Postgres roles 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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ------=_NextPart_000_010D_01C86A73.389BFF70 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi Shane,

 

You are exactly right.

My issue is that, I now have one role called sti - that has carried the = group members from the old version -  

So what do you think my options are, so I can separate them? =

I have only one option in my mind:

-         Revoke the = members from the role sti

-         Create a = new role (that has rolcanlogin set to false) called sti_group

-         Assign the = members to it

-         Finally, = fix all the permissions for all the tables (add the permissions to the new group = sti_group)
which seems like a huge amount of work…

 

In that case I’ll be able to give permissions such as = :

GRANT SELECT ON table TO sti_group;
GRANT SELECT,INSERT,UPDATE,DELETE ON table TO = sti;

 

Is there a better solution ?

 

Pascal

 

 

-----Original Message-----
From: pgsql-sql-owner@postgresql.org = [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Shane Ambler
Sent: Friday, February 08, 2008 3:54 PM
To: ptufenkji@usj.edu.lb
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Postgres roles

 

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 =3D '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

 

---------------------------(end of broadcast)---------------------------

TIP 3: Have you checked our extensive FAQ?

 

       &nbs= p;       http://www.postgresql.org/docs/faq

------=_NextPart_000_010D_01C86A73.389BFF70--