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