From: | "David E(dot) Wheeler" <david(at)kineticode(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Group and Role Disagreement |
Date: | 2009-12-31 23:07:06 |
Message-ID: | 02CF918D-EDDA-470E-82A5-97DF5CE6E6DF@kineticode.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Fellow Hackers,
Given this SQL:
BEGIN;
CREATE ROLE foo WITH NOLOGIN;
CREATE ROLE foo_bar WITH LOGIN PASSWORD '***' INHERIT IN ROLE foo;
CREATE ROLE foo_baz WITH LOGIN PASSWORD '***' INHERIT IN ROLE foo;
CREATE ROLE foo_yow WITH LOGIN PASSWORD '***' INHERIT
IN ROLE foo, foo_bar, foo_baz;
SELECT groname, array_agg(rolname)
FROM pg_group
JOIN pg_roles ON pg_roles.oid = ANY(grolist)
WHERE groname IN ('foo', 'foo_bar', 'foo_baz', 'foo_yow')
GROUP BY groname;
SELECT r.rolname,
ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as memberof
FROM pg_catalog.pg_roles r
WHERE rolname IN ('foo', 'foo_bar', 'foo_baz', 'foo_yow');
ROLLBACK;
The output is:
BEGIN
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
groname | array_agg
---------+---------------------------
foo | {foo_bar,foo_baz,foo_yow}
(1 row)
rolname | memberof
---------+-----------------------
foo | {}
foo_bar | {foo}
foo_baz | {foo}
foo_yow | {foo,foo_bar,foo_baz}
(4 rows)
ROLLBACK
My question is: why is the group membership of the foo_bar, foo_baz, and foo_yow roles not reflected in pg_group? Should it not have the same associations as pg_roles? A quick query shows that the only record in pg_group is for the "foo" group -- it doesn't even know that the foo_bar, foo_baz, and foo_yow roles also act as groups. Should it?
Thanks,
David
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-12-31 23:37:52 | Re: exec_execute_message crash |
Previous Message | Greg Stark | 2009-12-31 23:04:01 | Re: Serializable Isolation without blocking |