Re: Postgres roles - Mailing list pgsql-sql

<div class="Section1"><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">Hi
Shane,</span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">Youare exactly right.</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">Myissue is that, I now have one role called sti - that has carried the group members from the
oldversion -  </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">So
whatdo you think my options are, so I can separate them? </span></font><p class="MsoPlainText"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt">I have only one option in my mind:</span></font><p class="MsoPlainText"
style="margin-left:.25in;text-indent:-.25in;mso-list:
l0 level1 lfo1"><font face="Verdana" size="2"><span style="font-size:10.0pt"><span style="mso-list:Ignore">-<font
face="TimesNew Roman" size="1"><span style="font:7.0pt "Times New Roman"">        
</span></font></span></span></font><spandir="LTR">Revoke the members from the role sti</span><p class="MsoPlainText"
style="margin-left:.25in;text-indent:-.25in;mso-list:
l0 level1 lfo1"><font face="Verdana" size="2"><span style="font-size:10.0pt"><span style="mso-list:Ignore">-<font
face="TimesNew Roman" size="1"><span style="font:7.0pt "Times New Roman"">        
</span></font></span></span></font><spandir="LTR">Create a new role (that has rolcanlogin set to false) called
sti_group</span><pclass="MsoPlainText" style="margin-left:.25in;text-indent:-.25in;mso-list: 
l0 level1 lfo1"><font face="Verdana" size="2"><span style="font-size:10.0pt"><span style="mso-list:Ignore">-<font
face="TimesNew Roman" size="1"><span style="font:7.0pt "Times New Roman"">        
</span></font></span></span></font><spandir="LTR">Assign the members to it</span><p class="MsoPlainText"
style="margin-left:.25in;text-indent:-.25in;mso-list:
l0 level1 lfo1"><font face="Verdana" size="2"><span style="font-size:10.0pt"><span style="mso-list:Ignore">-<font
face="TimesNew Roman" size="1"><span style="font:7.0pt "Times New Roman"">        
</span></font></span></span></font><spandir="LTR">Finally, fix all the permissions for all the tables (add the
permissionsto the new group sti_group)<br /> which seems like a huge amount of work… </span><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">In that case I’ll be able to give
permissionssuch as :</span></font><p class="MsoPlainText"><i><font face="Verdana" size="2"><span
style="font-size:10.0pt;
font-style:italic">GRANT SELECT ON table TO sti_group;<br /> GRANT SELECT,INSERT,UPDATE,DELETE ON table TO
sti;</span></font></i><pclass="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">Isthere a better solution ?</span></font><p class="MsoPlainText"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt"> </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">Pascal</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">-----OriginalMessage-----<br /> From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Shane Ambler<br /> Sent: Friday, February 08, 2008 3:54 PM<br />
To:ptufenkji@usj.edu.lb<br /> Cc: pgsql-sql@postgresql.org<br /> Subject: Re: [SQL] Postgres roles</span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">Pascal Tufenkji wrote:</span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">> My questions are:</span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">> </span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">>  </span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">> </span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">> 1.      how do I identify the
usersassigned to this role :</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">>(in the older version)</span></font><p class="MsoPlainText"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt">> SELECT grolist from pg_group where groname = 'sti'; </span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">> </span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">"The view pg_group exists for
backwardscompatibility: it emulates a </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">catalogthat existed in PostgreSQL before version 8.1. It shows the </span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">names and members of all roles that
aremarked as not rolcanlogin, which </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">isan approximation to the set of roles that are being used as groups."</span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">Use pg_roles to get the user and group
info.Use pg_auth_members to get </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">thelist of members that belong to each group role.</span></font><p class="MsoPlainText"><font
face="Verdana"size="2"><span style="font-size:10.0pt">(any role can be used for a group but it is usually a role that
has</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">rolcanlogin set
tofalse, and has members recorded in pg_auth_members)</span></font><p class="MsoPlainText"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt"> </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">http://www.postgresql.org/docs/8.2/interactive/user-manag.html</span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">can explain it better - or more
specifically</span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">http://www.postgresql.org/docs/8.2/interactive/role-membership.html</span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">> </span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">> 2.      how do I differ granting
permissionson a table to the user sti from</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">>the whole members of the group sti</span></font><p class="MsoPlainText"><font
face="Verdana"size="2"><span style="font-size:10.0pt">> (in the older version)</span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">> GRANT SELECT ON table TO group
sti;</span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">> GRANT
SELECT,INSERT,UPDATE,DELETEON table TO sti;</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">></span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">Usea more descriptive name for the group or simply sti_group.</span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">I am guessing that you have an issue
becauseyou now have one role </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">calledsti - that has carried the group members from the old version - </span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">this is the admin userid used to login
butbecause it is used as a group </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">itpasses it's privileges to all members of sti.</span></font><p class="MsoPlainText"><font
face="Verdana"size="2"><span style="font-size:10.0pt"> </span></font><p class="MsoPlainText"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt"> </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">--</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">ShaneAmbler</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">pgSQL(at) Sheeky (dot) Biz</span></font><p class="MsoPlainText"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt"> </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">GetSheeky @ http://Sheeky.Biz</span></font><p class="MsoPlainText"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt"> </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">---------------------------(endof broadcast)---------------------------</span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">TIP 3: Have you checked our extensive
FAQ?</span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">              
http://www.postgresql.org/docs/faq</span></font></div>