From: Shane Ambler <pgsql@Sheeky.Biz>
To: ptufenkji@usj.edu.lb
Cc: pgsql-sql@postgresql.org
Subject: Re: Postgres roles
Date: Sun, 10 Feb 2008 00:46:21 +1030
Message-ID: <47ADB5B5.5070604@Sheeky.Biz> (raw)
In-Reply-To: <200802081442.m18Eg3F1025812@Citrus.usj.edu.lb>
References: <200802081442.m18Eg3F1025812@Citrus.usj.edu.lb>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pgsql@Sheeky.Biz, ptufenkji@usj.edu.lb
Subject: Re: Postgres roles
In-Reply-To: <47ADB5B5.5070604@Sheeky.Biz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox