Message-ID: <47ADB5B5.5070604@Sheeky.Biz>
Date: Sun, 10 Feb 2008 00:46:21 +1030
From: Shane Ambler <pgsql@Sheeky.Biz>
User-Agent: Thunderbird 2.0.0.6 (Macintosh/20070728)
MIME-Version: 1.0
To: ptufenkji@usj.edu.lb
CC: pgsql-sql@postgresql.org
Subject: Re: Postgres roles
References: <200802081442.m18Eg3F1025812@Citrus.usj.edu.lb>
In-Reply-To: <200802081442.m18Eg3F1025812@Citrus.usj.edu.lb>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Pascal Tufenkji wrote:
> 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 ?
> 

That is the solution and it does seem like a lot if you have lots of 
users and/or tables - I can think of a couple of ways to make it easy -

1. Use pgAdmin - it has a Grant wizard that will generate the sql for 
the grants and revokes on all the tables/functions etc for you. It can 
do an entire schema in a few clicks.

2. Generate the list of commands yourself - fill a text file with them 
and send them to psql.

"REVOKE sti FROM "+username+";"
"GRANT sti_group TO "+username+";"
"GRANT SELECT ON "+tablename+" TO sti_group;"
...
...


The second may be the way to go at least for the removing and adding 
group memberships from sti to sti_group as I don't see any helpers in 
pgAdmin for that.


-- 

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz