Thread: multiple membership grants and information_schema.applicable_roles

multiple membership grants and information_schema.applicable_roles

From
Pavel Luzanov
Date:
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



Re: multiple membership grants and information_schema.applicable_roles

From
Pavel Luzanov
Date:
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

From
Pavel Luzanov
Date:
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

From
Peter Eisentraut
Date:
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.