Re: Postgres roles

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