Re: multiple membership grants and information_schema.applicable_roles

Pavel Luzanov <p.luzanov@postgrespro.ru> writes:
> The application_roles view shows duplicates:

> postgres@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)

AFAICT this is also possible with the SQL standard's definition
of this view, so I don't see a bug here:

CREATE RECURSIVE VIEW APPLICABLE_ROLES ( GRANTEE, ROLE_NAME, IS_GRANTABLE ) AS
    ( ( SELECT GRANTEE, ROLE_NAME, IS_GRANTABLE
        FROM DEFINITION_SCHEMA.ROLE_AUTHORIZATION_DESCRIPTORS
        WHERE ( GRANTEE IN
                ( CURRENT_USER, 'PUBLIC' )
             OR
                GRANTEE IN
                ( SELECT ROLE_NAME
                  FROM ENABLED_ROLES ) ) )
      UNION
      ( SELECT RAD.GRANTEE, RAD.ROLE_NAME, RAD.IS_GRANTABLE
        FROM DEFINITION_SCHEMA.ROLE_AUTHORIZATION_DESCRIPTORS RAD
          JOIN
             APPLICABLE_ROLES R
            ON
               RAD.GRANTEE = R.ROLE_NAME ) );

The UNION would remove rows only when they are duplicates across all
three columns.

I do see what seems like a different issue: the standard appears to expect
that indirect role grants should also be shown (via the recursive CTE),
and we are not doing that.

            regards, tom lane