Skip site navigation (1) Skip section navigation (2)

Group and Role Disagreement

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 (view raw or flat)
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


Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2009-12-31 23:37:52
Subject: Re: exec_execute_message crash
Previous:From: Greg StarkDate: 2009-12-31 23:04:01
Subject: Re: Serializable Isolation without blocking

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group