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
