pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres roles
4+ messages / 2 participants
[nested] [flat]
* Postgres roles
@ 2008-02-08 13:02 Pascal Tufenkji <ptufenkji@usj.edu.lb>
2008-02-08 13:53 ` Re: Postgres roles Shane Ambler <pgsql@Sheeky.Biz>
0 siblings, 1 reply; 4+ messages in thread
From: Pascal Tufenkji @ 2008-02-08 13:02 UTC (permalink / raw)
To: pgsql-sql
Hi,
I'd like to ask you a question about users, groups and roles.
In older versions of Postgres we had users and groups as two separate
entities
Our IT department at work is called "sti", that's why:
- I had a user "sti" (the username of our IT manager)
- I had a group "sti" (the group containing all the users of our
department: user1, user2.)
When we upgraded to PostgreSQL 8.2.4, came up the concept of roles; users
and groups became one entity
So now I have in my database, only one role, called "sti"
My questions are:
1. how do I identify the users assigned to this role :
(in the older version)
SELECT grolist from pg_group where groname = 'sti';
2. how do I differ granting permissions on a table to the user sti from
the whole members of the group sti
(in the older version)
GRANT SELECT ON table TO group sti;
GRANT SELECT,INSERT,UPDATE,DELETE ON table TO sti;
I'd appreciate any help regarding this issue
Pascal Tufenkji
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres roles
2008-02-08 13:02 Postgres roles Pascal Tufenkji <ptufenkji@usj.edu.lb>
@ 2008-02-08 13:53 ` Shane Ambler <pgsql@Sheeky.Biz>
2008-02-08 14:54 ` Re: Postgres roles Pascal Tufenkji <ptufenkji@usj.edu.lb>
0 siblings, 1 reply; 4+ messages in thread
From: Shane Ambler @ 2008-02-08 13:53 UTC (permalink / raw)
To: ptufenkji@usj.edu.lb; +Cc: pgsql-sql
Pascal Tufenkji wrote:
> My questions are:
>
>
>
> 1. how do I identify the users assigned to this role :
> (in the older version)
> SELECT grolist from pg_group where groname = 'sti';
>
"The view pg_group exists for backwards compatibility: it emulates a
catalog that existed in PostgreSQL before version 8.1. It shows the
names and members of all roles that are marked as not rolcanlogin, which
is an approximation to the set of roles that are being used as groups."
Use pg_roles to get the user and group info. Use pg_auth_members to get
the list of members that belong to each group role.
(any role can be used for a group but it is usually a role that has
rolcanlogin set to false, and has members recorded in pg_auth_members)
http://www.postgresql.org/docs/8.2/interactive/user-manag.html
can explain it better - or more specifically
http://www.postgresql.org/docs/8.2/interactive/role-membership.html
>
> 2. how do I differ granting permissions on a table to the user sti from
> the whole members of the group sti
> (in the older version)
> GRANT SELECT ON table TO group sti;
> GRANT SELECT,INSERT,UPDATE,DELETE ON table TO sti;
>
Use a more descriptive name for the group or simply sti_group.
I am guessing that you have an issue because you now have one role
called sti - that has carried the group members from the old version -
this is the admin userid used to login but because it is used as a group
it passes it's privileges to all members of sti.
--
Shane Ambler
pgSQL (at) Sheeky (dot) Biz
Get Sheeky @ http://Sheeky.Biz
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres roles
2008-02-08 13:02 Postgres roles Pascal Tufenkji <ptufenkji@usj.edu.lb>
2008-02-08 13:53 ` Re: Postgres roles Shane Ambler <pgsql@Sheeky.Biz>
@ 2008-02-08 14:54 ` Pascal Tufenkji <ptufenkji@usj.edu.lb>
2008-02-09 14:16 ` Re: Postgres roles Shane Ambler <pgsql@Sheeky.Biz>
0 siblings, 1 reply; 4+ messages in thread
From: Pascal Tufenkji @ 2008-02-08 14:54 UTC (permalink / raw)
To: 'Shane Ambler' <pgsql@Sheeky.Biz>; +Cc: pgsql-sql
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 ?
Pascal
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Shane Ambler
Sent: Friday, February 08, 2008 3:54 PM
To: ptufenkji@usj.edu.lb
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Postgres roles
Pascal Tufenkji wrote:
> My questions are:
>
>
>
> 1. how do I identify the users assigned to this role :
> (in the older version)
> SELECT grolist from pg_group where groname = 'sti';
>
"The view pg_group exists for backwards compatibility: it emulates a
catalog that existed in PostgreSQL before version 8.1. It shows the
names and members of all roles that are marked as not rolcanlogin, which
is an approximation to the set of roles that are being used as groups."
Use pg_roles to get the user and group info. Use pg_auth_members to get
the list of members that belong to each group role.
(any role can be used for a group but it is usually a role that has
rolcanlogin set to false, and has members recorded in pg_auth_members)
http://www.postgresql.org/docs/8.2/interactive/user-manag.html
can explain it better - or more specifically
http://www.postgresql.org/docs/8.2/interactive/role-membership.html
>
> 2. how do I differ granting permissions on a table to the user sti
from
> the whole members of the group sti
> (in the older version)
> GRANT SELECT ON table TO group sti;
> GRANT SELECT,INSERT,UPDATE,DELETE ON table TO sti;
>
Use a more descriptive name for the group or simply sti_group.
I am guessing that you have an issue because you now have one role
called sti - that has carried the group members from the old version -
this is the admin userid used to login but because it is used as a group
it passes it's privileges to all members of sti.
--
Shane Ambler
pgSQL (at) Sheeky (dot) Biz
Get Sheeky @ http://Sheeky.Biz
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres roles
2008-02-08 13:02 Postgres roles Pascal Tufenkji <ptufenkji@usj.edu.lb>
2008-02-08 13:53 ` Re: Postgres roles Shane Ambler <pgsql@Sheeky.Biz>
2008-02-08 14:54 ` Re: Postgres roles Pascal Tufenkji <ptufenkji@usj.edu.lb>
@ 2008-02-09 14:16 ` Shane Ambler <pgsql@Sheeky.Biz>
0 siblings, 0 replies; 4+ messages in thread
From: Shane Ambler @ 2008-02-09 14:16 UTC (permalink / raw)
To: ptufenkji@usj.edu.lb; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-02-09 14:16 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-08 13:02 Postgres roles Pascal Tufenkji <ptufenkji@usj.edu.lb>
2008-02-08 13:53 ` Shane Ambler <pgsql@Sheeky.Biz>
2008-02-08 14:54 ` Pascal Tufenkji <ptufenkji@usj.edu.lb>
2008-02-09 14:16 ` Shane Ambler <pgsql@Sheeky.Biz>
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