Group and Role Disagreement
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
"David E. Wheeler" <david@kineticode.com> writes:
My question is: why is the group membership of the foo_bar, foo_baz,
and foo_yow roles not reflected in pg_group?
Per the fine manual:
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.
regards, tom lane
On Dec 31, 2009, at 3:41 PM, Tom Lane wrote:
My question is: why is the group membership of the foo_bar, foo_baz,
and foo_yow roles not reflected in pg_group?Per the fine manual:
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.
Ah, hadn't noticed that, thanks for the pointer to TFM.
Best,
David