Re: Changed functionality from 14.3 to 15.3

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(at)ewie(dot)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