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 <e...@ewie.name> 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
