pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUsers and groups
3+ messages / 3 participants
[nested] [flat]
* Users and groups
@ 2003-02-21 22:37 Teddy <epalo@ono.com>
0 siblings, 1 reply; 3+ messages in thread
From: Teddy @ 2003-02-21 22:37 UTC (permalink / raw)
To: pgsql-admin@postgresql.org; pgsql-general@postgresql.org; pgsql-sql
How can i list the users of a group?
Thanks.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Users and groups
@ 2003-03-01 13:02 Oliver Elphick <olly@lfix.co.uk>
parent: Teddy <epalo@ono.com>
0 siblings, 0 replies; 3+ messages in thread
From: Oliver Elphick @ 2003-03-01 13:02 UTC (permalink / raw)
To: Teddy <epalo@ono.com>; +Cc: pgsql-admin@postgresql.org; pgsql-general@postgresql.org; pgsql-sql
On Fri, 2003-02-21 at 22:37, Teddy wrote:
> How can i list the users of a group?
Someone posted this function a while back. (It's a multi-row function,
so it needs 7.3):
CREATE OR REPLACE TYPE group_view AS (grosysid int4, groname name, usesysid int4, usename name);
CREATE OR REPLACE FUNCTION expand_groups() RETURNS SETOF group_view AS '
DECLARE
rec record;
groview record;
low int;
high int;
BEGIN
FOR rec IN SELECT grosysid FROM pg_group LOOP
SELECT INTO low
replace(split_part(array_dims(grolist),'':'',1),''['','''')::int
FROM pg_group WHERE grosysid = rec.grosysid;
SELECT INTO high
replace(split_part(array_dims(grolist),'':'',2),'']'','''')::int
FROM pg_group WHERE grosysid = rec.grosysid;
FOR i IN low..high LOOP
SELECT INTO groview g.grosysid, g.groname, s.usesysid, s.usename
FROM pg_shadow s join pg_group g on s.usesysid = g.grolist[i]
WHERE grosysid = rec.grosysid;
RETURN NEXT groview;
END LOOP;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';
CREATE OR REPLACE VIEW public.pg_groupview AS SELECT * FROM expand_groups();
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"The LORD is my shepherd; I shall not want. He maketh
me to lie down in green pastures: he leadeth me beside
the still waters, he restoreth my soul...Surely
goodness and mercy shall follow me all the days of my
life; and I will dwell in the house of the LORD for
ever." Psalms 23:1,2,6
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Users and groups
@ 2003-03-03 09:58 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 3+ messages in thread
From: Christoph Haller @ 2003-03-03 09:58 UTC (permalink / raw)
To: pgsql-sql; +Cc: epalo@ono.com
>
> How can i list the users of a group?
>
This is taken from a former thread.
You'll have to adapt it.
Regards, Christoph
From: Michiel Lange <michiel@minas.demon.nl>
To: Joe Conway <mail@joeconway.com>
Subject: Re: [SQL] Could someone help me fix my array_list function?
Cc: Guy Fraser <guy@incentre.net>, pgsql-sql@postgresql.org
Date: Tue, 21 Jan 2003 01:38:13 +0100
Would the same work for pg_user and pg_group?
It would be handy at times to easily check wether or not someone is
member
of a group...
and since in pg_group the usernumbers are stored, one might need to do a
few lookups:
would it be hard to put such a thing in a view, or is that not-smart
thinking here?
I have to admit, arrays are still a bit hazy to me, in how to use them
properly in databases...
so I stick to the older solutions...
Michiel
At 15:27 20-1-2003 -0800, Joe Conway wrote:
>Guy Fraser wrote:
>>This is what I want to do:
>>select attribute,array_list(values,1,sizeof(values)) as value from
av_list;
>>Turn :
>> attr6 | {val3,val7,val4,val5}
>>Into :
>> attr6 | val3
>> attr6 | val7
>> attr6 | val4
>> attr6 | val5
>
>You didn't mention the version of PostgreSQL. If you're using < 7.3,
good
>luck ;-). If you are using 7.3, the following works:
>
>DROP TABLE mail_aliases;
>CREATE TABLE mail_aliases(
> a_mailbox text,
> a_destination text[]
>);
>
>INSERT INTO mail_aliases VALUES ('alias1', '{dest1}');
>INSERT INTO mail_aliases VALUES ('alias2', '{dest2,dest1}');
>INSERT INTO mail_aliases VALUES ('alias3', '{dest3,dest4}');
>INSERT INTO mail_aliases VALUES ('alias4', '{dest3,dest4,dest5}');
>INSERT INTO mail_aliases VALUES ('alias5', '{dest6,dest7}');
>INSERT INTO mail_aliases VALUES ('alias6',
'{dest3,dest7,dest4,dest5}');
>
>CREATE TYPE mail_aliases_list_type AS (a_mailbox text, a_destination_el
text);
>CREATE OR REPLACE FUNCTION mail_aliases_list() RETURNS SETOF
>mail_aliases_list_type AS '
>DECLARE
> rec record;
> retrec record;
> low int;
> high int;
>BEGIN
> FOR rec IN SELECT a_mailbox, a_destination FROM mail_aliases LOOP
> SELECT INTO low
>
>replace(split_part(array_dims(rec.a_destination),'':'',1),''['','''')::int;
> SELECT INTO high
>
>replace(split_part(array_dims(rec.a_destination),'':'',2),'']'','''')::int;
>
> FOR i IN low..high LOOP
> SELECT INTO retrec rec.a_mailbox, rec.a_destination[i];
> RETURN NEXT retrec;
> END LOOP;
> END LOOP;
> RETURN;
>END;
>' LANGUAGE 'plpgsql';
>
>regression=# SELECT a_mailbox, a_destination_el FROM
mail_aliases_list();
> a_mailbox | a_destination_el
>-----------+------------------
> alias1 | dest1
> alias2 | dest2
> alias2 | dest1
> alias3 | dest3
> alias3 | dest4
> alias4 | dest3
> alias4 | dest4
> alias4 | dest5
> alias5 | dest6
> alias5 | dest7
> alias6 | dest3
> alias6 | dest7
> alias6 | dest4
> alias6 | dest5
>(14 rows)
>
>
>HTH,
>
>Joe
>
From: Joe Conway <mail@joeconway.com>
To: Michiel Lange <michiel@minas.demon.nl>
Cc: Guy Fraser <guy@incentre.net>, pgsql-sql@postgresql.org
Subject: Re: [SQL] Could someone help me fix my array_list function?
Date: Mon, 20 Jan 2003 17:45:44 -0800
Michiel Lange wrote:
> Would the same work for pg_user and pg_group?
>
See:
http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_id=11378
With these groups:
regression=# select * from pg_group;
groname | grosysid | grolist
---------+----------+---------------
grp1 | 100 | {100,101,102}
grp2 | 101 | {100,102}
(2 rows)
Output looks like:
regression=# select * from groupview;
grosysid | groname | usesysid | usename
----------+---------+----------+---------
100 | grp1 | 100 | user1
100 | grp1 | 101 | user2
100 | grp1 | 102 | user3
101 | grp2 | 100 | user1
101 | grp2 | 102 | user3
(5 rows)
Joe
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-03-03 09:58 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-21 22:37 Users and groups Teddy <epalo@ono.com>
2003-03-01 13:02 ` Oliver Elphick <olly@lfix.co.uk>
2003-03-03 09:58 Re: Users and groups Christoph Haller <ch@rodos.fzk.de>
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