[HACKERS] Group and Role Disagreement

[David E. Wheeler]

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers