Re: Changed functionality from 14.3 to 15.3 - Mailing list pgsql-general

On 2023-09-20 13:17 -0400, Michael Corey wrote:
> PG 14 Server
> psql (14.2, server 14.3)
> You are now connected to database "db14" as user "postgres".
> db14=>  \dn+ ref_schema|sten_schema
>                               List of schemas
>     Name     |    Owner    |        Access privileges        | Description
> -------------+-------------+---------------------------------+-------------
>  ref_schema  | ref_schema  | ref_schema=UC/ref_schema       +|
>              |             | sten_schema=U/ref_schema       +|
>              |             | ref_schema_read=U/ref_schema   +|
>              |             | ref_schema_write=U/ref_schema   |
>  sten_schema | sten_schema | sten_schema=UC/sten_schema     +|
>              |             | ref_schema=U/sten_schema       +|
>              |             | sten_schema_read=U/sten_schema +|
>              |             | sten_schema_write=U/sten_schema |
> (2 rows)
> 
> 
> db14=> \dp ref_schema.ref_media_code
>                                           Access privileges
>    Schema   |      Name      | Type  |       Access privileges        | Column privileges | Policies
> ------------+----------------+-------+--------------------------------+-------------------+----------
>  ref_schema | ref_media_code | table | ref_schema=arwdDxt/ref_schema +|                   |
>             |                |       | ref_schema_read=r/ref_schema  +|                   |
>             |                |       | sten_schema_write=r/ref_schema |                   |
> (1 row)
> 
> 
> db14=> \dp sten_schema.sten_media_codes_view
>                                               Access privileges
>    Schema    |         Name          | Type |        Access privileges        | Column privileges | Policies
> -------------+-----------------------+------+---------------------------------+-------------------+----------
>  sten_schema | sten_media_codes_view | view | sten_schema=arwdDxt/sten_schema+|                   |
>              |                       |      | sten_schema_write=r/sten_schema |                   |
> (1 row)
> 
> PG 15 server
> psql (14.2, server 15.3)
> You are now connected to database "db14" as user "postgres".
> db14=>  \dn+ ref_schema|sten_schema
>                               List of schemas
>     Name     |    Owner    |        Access privileges        | Description
> -------------+-------------+---------------------------------+-------------
>  ref_schema  | ref_schema  | ref_schema=UC/ref_schema       +|
>              |             | sten_schema=U/ref_schema       +|
>              |             | ref_schema_read=U/ref_schema   +|
>              |             | ref_schema_write=U/ref_schema   |
>  sten_schema | sten_schema | sten_schema=UC/sten_schema     +|
>              |             | ref_schema=U/sten_schema       +|
>              |             | sten_schema_read=U/sten_schema +|
>              |             | sten_schema_write=U/sten_schema |
> (2 rows)
> 
> 
> db14=> \dp ref_schema.ref_media_code
>                                           Access privileges
>    Schema   |      Name      | Type  |       Access privileges        | Column privileges | Policies
> ------------+----------------+-------+--------------------------------+-------------------+----------
>  ref_schema | ref_media_code | table | ref_schema=arwdDxt/ref_schema +|                   |
>             |                |       | ref_schema_read=r/ref_schema  +|                   |
>             |                |       | sten_schema_write=r/ref_schema |                   |
> (1 row)
> 
> 
> db14=> \dp sten_schema.sten_media_codes_view
>                                               Access privileges
>    Schema    |         Name          | Type |        Access privileges        | Column privileges | Policies
> -------------+-----------------------+------+---------------------------------+-------------------+----------
>  sten_schema | sten_media_codes_view | view | sten_schema=arwdDxt/sten_schema+|                   |
>              |                       |      | sten_schema_write=r/sten_schema |                   |
> (1 row)

Thanks.  Those privileges are identical.  But sten_schema has no SELECT
privilege on table ref_media_code on either server.  That's necessary
when querying through view sten_media_codes_view.  And there's also no
GRANT for that in your script.  I somehow missed that previously.

> "And both databases start empty or at least in the exact same state?"
> Yes, this is a test case, so I created two new databases one in 14.3 and
> one in 15.3, did the setup as I provided, and ran the two SELECTs in both
> databases and received different results.

Now I had the time to run your script and I can reproduce the missing
privileges on both 14.3 and 15.3.

Has your 14.3 some left-over state from previous test runs?  I assume
the server is not re-created for each test run.  I was wondering if the
roles may still exist and with additional memberships.  But then again
the script just uses CREATE ROLE.  So the roles definitely do not exist
beforehand.  But what are the actual memberships of sten_schema?
Because it must inherit SELECT on ref_media_code on 14.3.  It can't be
from object_creator because that role also gets newly created.

-- 
Erik