Re: Changed functionality from 14.3 to 15.3 - Mailing list pgsql-general
| From | Michael Corey |
|---|---|
| Subject | Re: Changed functionality from 14.3 to 15.3 |
| Date | |
| Msg-id | CAABu8T_H7uXVO_Jz3tCUMn8rJyA8YzoWpQEch5LjTucccMBQcg@mail.gmail.com Whole thread Raw |
| In response to | Re: Changed functionality from 14.3 to 15.3 (Erik Wienhold <ewie@ewie.name>) |
| Responses |
Re: Changed functionality from 14.3 to 15.3
|
| List | pgsql-general |
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)
"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.
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)
"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.
On Wed, Sep 20, 2023 at 12:33 PM Erik Wienhold <ewie@ewie.name> wrote:
On 2023-09-20 09:15 -0400, Michael Corey wrote:
> Thanks for responding. All of the DDL is just the setup for the test
> case. I ran those steps in both databases to setup the exact same
> environment.
And both databases start empty or at least in the exact same state?
> The COMMIT is not needed for the test out of habit I put it
> in my setup. The main issue is in 14.3 I can run this select as user
> sten_schema, but in 15.3 I am unable due to a permission issue.
I don't know why those privileges could be missing if that script
completes. Please provide the privileges like I asked to see what's
actually granted on both databases:
> On Tue, Sep 19, 2023 at 8:17 PM Erik Wienhold <ewie@ewie.name> wrote:
> >
> > Have you checked that the permissions are actually the same on both
> > databases after running that script?
> >
> > \dn+ ref_schema|sten_schema
> > \dp ref_schema.ref_media_code
> > \dp sten_schema.sten_media_codes_view
--
Erik
Michael Corey
pgsql-general by date: