Thread: multiple membership grants and information_schema.applicable_roles
I found that multiple membership grants added in v16 affects the information_schema.applicable_roles view. Examples on a master, but they works for v16 too. Setup multiple membership alice in bob: postgres@postgres(17.0)=# \drg alice List of role grants Role name | Member of | Options | Grantor -----------+-----------+--------------+---------- alice | bob | INHERIT, SET | alice alice | bob | INHERIT, SET | charlie alice | bob | ADMIN | postgres (3 rows) 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) View definition: postgres@postgres(17.0)=# \sv information_schema.applicable_roles CREATE OR REPLACE VIEW information_schema.applicable_roles AS SELECT a.rolname::information_schema.sql_identifier AS grantee, b.rolname::information_schema.sql_identifier AS role_name, CASE WHEN m.admin_option THEN 'YES'::text ELSE 'NO'::text END::information_schema.yes_or_no AS is_grantable FROM ( SELECT pg_auth_members.member, pg_auth_members.roleid, pg_auth_members.admin_option FROM pg_auth_members UNION SELECT pg_database.datdba, pg_authid.oid, false FROM pg_database, pg_authid WHERE pg_database.datname = current_database() AND pg_authid.rolname = 'pg_database_owner'::name) m JOIN pg_authid a ON m.member = a.oid JOIN pg_authid b ON m.roleid = b.oid WHERE pg_has_role(a.oid, 'USAGE'::text); I think that only one row with admin option should be returned. This can be achieved by adding group by + bool_or to the inner select from pg_auth_members. BEGIN; BEGIN postgres@postgres(17.0)=*# CREATE OR REPLACE VIEW information_schema.applicable_roles AS SELECT a.rolname::information_schema.sql_identifier AS grantee, b.rolname::information_schema.sql_identifier AS role_name, CASE WHEN m.admin_option THEN 'YES'::text ELSE 'NO'::text END::information_schema.yes_or_no AS is_grantable FROM ( SELECT pg_auth_members.member, pg_auth_members.roleid, bool_or(pg_auth_members.admin_option) AS admin_option FROM pg_auth_members GROUP BY 1, 2 UNION SELECT pg_database.datdba, pg_authid.oid, false FROM pg_database, pg_authid WHERE pg_database.datname = current_database() AND pg_authid.rolname = 'pg_database_owner'::name) m JOIN pg_authid a ON m.member = a.oid JOIN pg_authid b ON m.roleid = b.oid WHERE pg_has_role(a.oid, 'USAGE'::text); CREATE VIEW postgres@postgres(17.0)=*# SELECT * FROM information_schema.applicable_roles WHERE grantee = 'alice'; grantee | role_name | is_grantable ---------+-----------+-------------- alice | bob | YES (1 row) postgres@postgres(17.0)=*# ROLLBACK; ROLLBACK Should we add group by + bool_or to the applicable_roles view? -- Pavel Luzanov Postgres Professional: https://postgrespro.com
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
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
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
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.