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.