Re: multiple membership grants and information_schema.applicable_roles
On 24.07.23 08:42, Pavel Luzanov wrote: 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. The implementation of the information_schema.applicable_roles view predates both indirect role grants and recursive query support. So some updates might be in order.
Re: multiple membership grants and information_schema.applicable_roles
On 24.07.2023 09:42, Pavel Luzanov wrote: Is IS_GRANTABLE a key column for ROLE_AUTHORIZATION_DESCRIPTORS? If not, duplicates is not possible. Right? The answer is: no. Duplicate pairs (grantee, role_name) is impossible only with defined key with this two columns. If there is no such key or key contain another column, for example grantor, then the information_schema.applicable_roles view definition is correct in this part. -- Pavel Luzanov Postgres Professional: https://postgrespro.com
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
Re: multiple membership grants and information_schema.applicable_roles
Pavel Luzanov 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
