Pavel Luzanov <p.luza...@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