multiple membership grants and information_schema.applicable_roles

I found that multiple membership grants added in v16 affects the
information_schema.applicable_roles view.

Examples on a master, but they works for v16 too.

Setup multiple membership alice in bob:

postgres(at)postgres(17.0)=# \drg alice
               List of role grants
 Role name | Member of |   Options    | Grantor
-----------+-----------+--------------+----------
 alice     | bob       | INHERIT, SET | alice
 alice     | bob       | INHERIT, SET | charlie
 alice     | bob       | ADMIN        | postgres
(3 rows)

The application_roles view shows duplicates:

postgres(at)postgres(17.0)=# SELECT * FROM
information_schema.applicable_roles WHERE grantee = 'alice';
 grantee | role_name | is_grantable
---------+-----------+--------------
 alice   | bob       | NO
 alice   | bob       | YES
(2 rows)

View definition:

postgres(at)postgres(17.0)=# \sv information_schema.applicable_roles
CREATE OR REPLACE VIEW information_schema.applicable_roles AS
 SELECT a.rolname::information_schema.sql_identifier AS grantee,
    b.rolname::information_schema.sql_identifier AS role_name,
        CASE
            WHEN m.admin_option THEN 'YES'::text
            ELSE 'NO'::text
        END::information_schema.yes_or_no AS is_grantable
   FROM ( SELECT pg_auth_members.member,
            pg_auth_members.roleid,
            pg_auth_members.admin_option
           FROM pg_auth_members
        UNION
         SELECT pg_database.datdba,
            pg_authid.oid,
            false
           FROM pg_database,
            pg_authid
          WHERE pg_database.datname = current_database() AND
pg_authid.rolname = 'pg_database_owner'::name) m
     JOIN pg_authid a ON m.member = a.oid
     JOIN pg_authid b ON m.roleid = b.oid
  WHERE pg_has_role(a.oid, 'USAGE'::text);

I think that only one row with admin option should be returned.
This can be achieved by adding group by + bool_or to the inner select
from pg_auth_members.

BEGIN;
BEGIN
postgres(at)postgres(17.0)=*# CREATE OR REPLACE VIEW
information_schema.applicable_roles AS
SELECT a.rolname::information_schema.sql_identifier AS grantee,
    b.rolname::information_schema.sql_identifier AS role_name,
        CASE
            WHEN m.admin_option THEN 'YES'::text
            ELSE 'NO'::text
        END::information_schema.yes_or_no AS is_grantable
   FROM ( SELECT pg_auth_members.member,
            pg_auth_members.roleid,
            bool_or(pg_auth_members.admin_option) AS admin_option
           FROM pg_auth_members
           GROUP BY 1, 2
        UNION
         SELECT pg_database.datdba,
            pg_authid.oid,
            false
           FROM pg_database,
            pg_authid
          WHERE pg_database.datname = current_database() AND
pg_authid.rolname = 'pg_database_owner'::name) m
     JOIN pg_authid a ON m.member = a.oid
     JOIN pg_authid b ON m.roleid = b.oid
  WHERE pg_has_role(a.oid, 'USAGE'::text);
CREATE VIEW
postgres(at)postgres(17.0)=*# SELECT * FROM
information_schema.applicable_roles WHERE grantee = 'alice';
 grantee | role_name | is_grantable
---------+-----------+--------------
 alice   | bob       | YES
(1 row)

postgres(at)postgres(17.0)=*# ROLLBACK;
ROLLBACK

Should we add group by + bool_or to the applicable_roles view?

--
Pavel Luzanov
Postgres Professional: https://postgrespro.com