Re: multiple membership grants and information_schema.applicable_roles

On 23.07.2023 23:03, Tom Lane wrote:
> 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.

Hm, I think there is one more thing to check in the SQL standard.
Is IS_GRANTABLE a key column for ROLE_AUTHORIZATION_DESCRIPTORS?
If not, duplicates is not possible. Right?

Can't check now, since I don't have access to the SQL standard definition.

> 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.

I noticed this, but the view stays unchanged so long time.
I thought it was done intentionally.

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