Group and Role Disagreement

Started by David E. Wheelerabout 16 years ago3 messages
#1David E. Wheeler
david@kineticode.com

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: David E. Wheeler (#1)
Re: Group and Role Disagreement

"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

#3David E. Wheeler
david@kineticode.com
In reply to: Tom Lane (#2)
Re: Group and Role Disagreement

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