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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
