Erik,
Just to be clear in your last response are you saying on your 14.3 you are getting the
ERROR: permission denied for table ref_media_code ?
If this is true then it seems to be something in our setup. This database may have been upgraded from 13.x to 14. The sten_schema has INHERIT when I create, but that does not mean INHERIT from ref_schema, correct?
All the items I have created just once I have not removed or recreated any of these for my test. The problem is impacting my real actual schemas and was discovered after we did the upgrade to 15. I decided then to restore the original 14 server and made two copies. I kept one as 14 and upgraded the other to 15. Lastly, I created the test case.
On Wed, Sep 20, 2023 at 3:07 PM Erik Wienhold <ewie@xxxxxxxxx> wrote:
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
Michael Corey
