Thread: Changed functionality from 14.3 to 15.3

Changed functionality from 14.3 to 15.3

From
Michael Corey
Date:
We are experiencing different functionality once we upgraded from Postgres 14.3 to Postgres 15.3.  

Below is a test case that we created which shows a schema user who has a VIEW that accesses a table in another schema.  In 14.3 the schema user is able to create the VIEW against the other schema's table and successfully SELECT data from that VIEW as well as directly from the other schema's table.

In 15.3 the same setup does allow for the VIEW to be created however, the schema user is unable to SELECT data using the VIEW or directly from the user's table.

Is anyone aware of changes that would cause this functionality to stop working?

--
-- Super Roles
CREATE ROLE object_creator NOLOGIN NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE NOREPLICATION ;
GRANT rds_superuser TO object_creator;

--
-- Common Roles

CREATE ROLE ref_schema_read ;
CREATE ROLE ref_schema_write ;

CREATE ROLE sten_schema_read ;
CREATE ROLE sten_schema_write ;

--
-- User = sten_schema

CREATE ROLE sten_schema ;
ALTER ROLE sten_schema WITH LOGIN INHERIT ;
ALTER ROLE sten_schema IN DATABASE db14 SET search_path TO "$user", ref_schema, public;
GRANT object_creator TO  sten_schema ;

--
-- User = ref_schema

CREATE ROLE ref_schema ;
ALTER ROLE ref_schema WITH LOGIN INHERIT ;
ALTER ROLE ref_schema IN DATABASE db14 SET search_path TO "$user", sten_schema, public;
GRANT object_creator TO  ref_schema ;

-- Schema = ref_schema
-- Permissions on schema are:

CREATE SCHEMA IF NOT EXISTS ref_schema ;
ALTER SCHEMA ref_schema OWNER TO ref_schema;

GRANT ALL ON SCHEMA ref_schema TO ref_schema;
GRANT USAGE ON SCHEMA ref_schema TO sten_schema;
GRANT USAGE ON SCHEMA ref_schema TO ref_schema_read;
GRANT USAGE ON SCHEMA ref_schema TO ref_schema_write;

--
-- Table

CREATE TABLE IF NOT EXISTS ref_schema.ref_media_code
(
    media_code character varying(10) COLLATE pg_catalog."default" NOT NULL
) ;

ALTER TABLE IF EXISTS ref_schema.ref_media_code OWNER to ref_schema;

GRANT ALL ON TABLE ref_schema.ref_media_code TO ref_schema;
GRANT SELECT ON TABLE ref_schema.ref_media_code TO ref_schema_read;
GRANT SELECT ON TABLE ref_schema.ref_media_code TO sten_schema_write;

insert into ref_schema.ref_media_code values ('CODE1') ;
insert into ref_schema.ref_media_code values ('CODE2') ;
insert into ref_schema.ref_media_code values ('CODE3') ;
commit ;

-- Schema = sten_schema
-- Permissions on schema are:

CREATE SCHEMA IF NOT EXISTS sten_schema ;
ALTER SCHEMA sten_schema OWNER TO sten_schema;

GRANT ALL ON SCHEMA sten_schema TO sten_schema;
GRANT USAGE ON SCHEMA sten_schema TO ref_schema;
GRANT USAGE ON SCHEMA sten_schema TO sten_schema_read;
GRANT USAGE ON SCHEMA sten_schema TO sten_schema_write;

CREATE OR REPLACE VIEW sten_schema.sten_media_codes_view
 AS
 SELECT mc.media_code
   FROM ref_schema.ref_media_code mc;

ALTER TABLE sten_schema.sten_media_codes_view OWNER TO sten_schema;

GRANT ALL ON TABLE sten_schema.sten_media_codes_view TO sten_schema;
GRANT SELECT ON TABLE sten_schema.sten_media_codes_view TO sten_schema_write;

*******************************************************************

--
-- Postgres 14.3 TEST
--
postgres=> \c db14 sten_schema
Password for user sten_schema:
psql (14.2, server 14.3)
You are now connected to database "db14" as user "sten_schema".

db14=> select * from sten_media_codes_view ;
 media_code
------------
 CODE1
 CODE2
 CODE3
(3 rows)

db14=> select * from ref_media_code ;
 media_code
------------
 CODE1
 CODE2
 CODE3
(3 rows)

************************************************

--
-- Postgres 15.3 TEST
--

postgres=> \c db14 sten_schema
Password for user sten_schema:
psql (14.2, server 15.3)
You are now connected to database "db14" as user "sten_schema".

db14=> select * from sten_media_codes_view ;
ERROR:  permission denied for table ref_media_code
db14=> select * from ref_media_code ;
ERROR:  permission denied for table ref_media_code
db14=>

--
M

Re: Changed functionality from 14.3 to 15.3

From
Erik Wienhold
Date:
On 2023-09-19 15:09 -0400, Michael Corey wrote:
> We are experiencing different functionality once we upgraded from Postgres
> 14.3 to Postgres 15.3.
> 
> Below is a test case that we created which shows a schema user who has a
> VIEW that accesses a table in another schema.  In 14.3 the schema user is
> able to create the VIEW against the other schema's table and successfully
> SELECT data from that VIEW as well as directly from the other schema's
> table.
> 
> In 15.3 the same setup does allow for the VIEW to be created however, the
> schema user is unable to SELECT data using the VIEW or directly from the
> user's table.
> 
> Is anyone aware of changes that would cause this functionality to stop
> working?
> 
> --
> -- Super Roles
> CREATE ROLE object_creator NOLOGIN NOSUPERUSER NOINHERIT NOCREATEDB
> NOCREATEROLE NOREPLICATION ;
> GRANT rds_superuser TO object_creator;
> 
> --
> -- Common Roles
> 
> CREATE ROLE ref_schema_read ;
> CREATE ROLE ref_schema_write ;
> 
> CREATE ROLE sten_schema_read ;
> CREATE ROLE sten_schema_write ;
> 
> --
> -- User = sten_schema
> 
> CREATE ROLE sten_schema ;
> ALTER ROLE sten_schema WITH LOGIN INHERIT ;
> ALTER ROLE sten_schema IN DATABASE db14 SET search_path TO "$user",
> ref_schema, public;
> GRANT object_creator TO  sten_schema ;
> 
> --
> -- User = ref_schema
> 
> CREATE ROLE ref_schema ;
> ALTER ROLE ref_schema WITH LOGIN INHERIT ;
> ALTER ROLE ref_schema IN DATABASE db14 SET search_path TO "$user",
> sten_schema, public;
> GRANT object_creator TO  ref_schema ;
> 
> -- Schema = ref_schema
> -- Permissions on schema are:
> 
> CREATE SCHEMA IF NOT EXISTS ref_schema ;
> ALTER SCHEMA ref_schema OWNER TO ref_schema;
> 
> GRANT ALL ON SCHEMA ref_schema TO ref_schema;
> GRANT USAGE ON SCHEMA ref_schema TO sten_schema;
> GRANT USAGE ON SCHEMA ref_schema TO ref_schema_read;
> GRANT USAGE ON SCHEMA ref_schema TO ref_schema_write;
> 
> --
> -- Table
> 
> CREATE TABLE IF NOT EXISTS ref_schema.ref_media_code
> (
>     media_code character varying(10) COLLATE pg_catalog."default" NOT NULL
> ) ;
> 
> ALTER TABLE IF EXISTS ref_schema.ref_media_code OWNER to ref_schema;
> 
> GRANT ALL ON TABLE ref_schema.ref_media_code TO ref_schema;
> GRANT SELECT ON TABLE ref_schema.ref_media_code TO ref_schema_read;
> GRANT SELECT ON TABLE ref_schema.ref_media_code TO sten_schema_write;
> 
> insert into ref_schema.ref_media_code values ('CODE1') ;
> insert into ref_schema.ref_media_code values ('CODE2') ;
> insert into ref_schema.ref_media_code values ('CODE3') ;
> commit ;

This COMMIT statement is suspicious because I don't see an explicit
transaction start.  Does the script run with autocommit=off and does it
even finish without error?  Everything after this transaction may have
been rolled back.

If sten_schema.sten_media_codes_view already exists (after all, you're
using IF NOT EXISTS) and the rest of the script fails (without
autocommit) you'll end up with the original view and schema and whatever
permissions they had to begin with.

> -- Schema = sten_schema
> -- Permissions on schema are:
> 
> CREATE SCHEMA IF NOT EXISTS sten_schema ;
> ALTER SCHEMA sten_schema OWNER TO sten_schema;
> 
> GRANT ALL ON SCHEMA sten_schema TO sten_schema;
> GRANT USAGE ON SCHEMA sten_schema TO ref_schema;
> GRANT USAGE ON SCHEMA sten_schema TO sten_schema_read;
> GRANT USAGE ON SCHEMA sten_schema TO sten_schema_write;
> 
> CREATE OR REPLACE VIEW sten_schema.sten_media_codes_view
>  AS
>  SELECT mc.media_code
>    FROM ref_schema.ref_media_code mc;
> 
> ALTER TABLE sten_schema.sten_media_codes_view OWNER TO sten_schema;
> 
> GRANT ALL ON TABLE sten_schema.sten_media_codes_view TO sten_schema;
> GRANT SELECT ON TABLE sten_schema.sten_media_codes_view TO
> sten_schema_write;
> 
> *******************************************************************
> 
> --
> -- Postgres 14.3 TEST
> --
> postgres=> \c db14 sten_schema
> Password for user sten_schema:
> psql (14.2, server 14.3)
> You are now connected to database "db14" as user "sten_schema".
> 
> db14=> select * from sten_media_codes_view ;
>  media_code
> ------------
>  CODE1
>  CODE2
>  CODE3
> (3 rows)
> 
> db14=> select * from ref_media_code ;
>  media_code
> ------------
>  CODE1
>  CODE2
>  CODE3
> (3 rows)
> 
> ************************************************
> 
> --
> -- Postgres 15.3 TEST
> --
> 
> postgres=> \c db14 sten_schema
> Password for user sten_schema:
> psql (14.2, server 15.3)
> You are now connected to database "db14" as user "sten_schema".
> 
> db14=> select * from sten_media_codes_view ;
> ERROR:  permission denied for table ref_media_code
> db14=> select * from ref_media_code ;
> ERROR:  permission denied for table ref_media_code
> db14=>

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



Re: Changed functionality from 14.3 to 15.3

From
Michael Corey
Date:
Erik,

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.  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.



On Tue, Sep 19, 2023 at 8:17 PM Erik Wienhold <ewie@ewie.name> wrote:
On 2023-09-19 15:09 -0400, Michael Corey wrote:
> We are experiencing different functionality once we upgraded from Postgres
> 14.3 to Postgres 15.3.
>
> Below is a test case that we created which shows a schema user who has a
> VIEW that accesses a table in another schema.  In 14.3 the schema user is
> able to create the VIEW against the other schema's table and successfully
> SELECT data from that VIEW as well as directly from the other schema's
> table.
>
> In 15.3 the same setup does allow for the VIEW to be created however, the
> schema user is unable to SELECT data using the VIEW or directly from the
> user's table.
>
> Is anyone aware of changes that would cause this functionality to stop
> working?
>
> --
> -- Super Roles
> CREATE ROLE object_creator NOLOGIN NOSUPERUSER NOINHERIT NOCREATEDB
> NOCREATEROLE NOREPLICATION ;
> GRANT rds_superuser TO object_creator;
>
> --
> -- Common Roles
>
> CREATE ROLE ref_schema_read ;
> CREATE ROLE ref_schema_write ;
>
> CREATE ROLE sten_schema_read ;
> CREATE ROLE sten_schema_write ;
>
> --
> -- User = sten_schema
>
> CREATE ROLE sten_schema ;
> ALTER ROLE sten_schema WITH LOGIN INHERIT ;
> ALTER ROLE sten_schema IN DATABASE db14 SET search_path TO "$user",
> ref_schema, public;
> GRANT object_creator TO  sten_schema ;
>
> --
> -- User = ref_schema
>
> CREATE ROLE ref_schema ;
> ALTER ROLE ref_schema WITH LOGIN INHERIT ;
> ALTER ROLE ref_schema IN DATABASE db14 SET search_path TO "$user",
> sten_schema, public;
> GRANT object_creator TO  ref_schema ;
>
> -- Schema = ref_schema
> -- Permissions on schema are:
>
> CREATE SCHEMA IF NOT EXISTS ref_schema ;
> ALTER SCHEMA ref_schema OWNER TO ref_schema;
>
> GRANT ALL ON SCHEMA ref_schema TO ref_schema;
> GRANT USAGE ON SCHEMA ref_schema TO sten_schema;
> GRANT USAGE ON SCHEMA ref_schema TO ref_schema_read;
> GRANT USAGE ON SCHEMA ref_schema TO ref_schema_write;
>
> --
> -- Table
>
> CREATE TABLE IF NOT EXISTS ref_schema.ref_media_code
> (
>     media_code character varying(10) COLLATE pg_catalog."default" NOT NULL
> ) ;
>
> ALTER TABLE IF EXISTS ref_schema.ref_media_code OWNER to ref_schema;
>
> GRANT ALL ON TABLE ref_schema.ref_media_code TO ref_schema;
> GRANT SELECT ON TABLE ref_schema.ref_media_code TO ref_schema_read;
> GRANT SELECT ON TABLE ref_schema.ref_media_code TO sten_schema_write;
>
> insert into ref_schema.ref_media_code values ('CODE1') ;
> insert into ref_schema.ref_media_code values ('CODE2') ;
> insert into ref_schema.ref_media_code values ('CODE3') ;
> commit ;

This COMMIT statement is suspicious because I don't see an explicit
transaction start.  Does the script run with autocommit=off and does it
even finish without error?  Everything after this transaction may have
been rolled back.

If sten_schema.sten_media_codes_view already exists (after all, you're
using IF NOT EXISTS) and the rest of the script fails (without
autocommit) you'll end up with the original view and schema and whatever
permissions they had to begin with.

> -- Schema = sten_schema
> -- Permissions on schema are:
>
> CREATE SCHEMA IF NOT EXISTS sten_schema ;
> ALTER SCHEMA sten_schema OWNER TO sten_schema;
>
> GRANT ALL ON SCHEMA sten_schema TO sten_schema;
> GRANT USAGE ON SCHEMA sten_schema TO ref_schema;
> GRANT USAGE ON SCHEMA sten_schema TO sten_schema_read;
> GRANT USAGE ON SCHEMA sten_schema TO sten_schema_write;
>
> CREATE OR REPLACE VIEW sten_schema.sten_media_codes_view
>  AS
>  SELECT mc.media_code
>    FROM ref_schema.ref_media_code mc;
>
> ALTER TABLE sten_schema.sten_media_codes_view OWNER TO sten_schema;
>
> GRANT ALL ON TABLE sten_schema.sten_media_codes_view TO sten_schema;
> GRANT SELECT ON TABLE sten_schema.sten_media_codes_view TO
> sten_schema_write;
>
> *******************************************************************
>
> --
> -- Postgres 14.3 TEST
> --
> postgres=> \c db14 sten_schema
> Password for user sten_schema:
> psql (14.2, server 14.3)
> You are now connected to database "db14" as user "sten_schema".
>
> db14=> select * from sten_media_codes_view ;
>  media_code
> ------------
>  CODE1
>  CODE2
>  CODE3
> (3 rows)
>
> db14=> select * from ref_media_code ;
>  media_code
> ------------
>  CODE1
>  CODE2
>  CODE3
> (3 rows)
>
> ************************************************
>
> --
> -- Postgres 15.3 TEST
> --
>
> postgres=> \c db14 sten_schema
> Password for user sten_schema:
> psql (14.2, server 15.3)
> You are now connected to database "db14" as user "sten_schema".
>
> db14=> select * from sten_media_codes_view ;
> ERROR:  permission denied for table ref_media_code
> db14=> select * from ref_media_code ;
> ERROR:  permission denied for table ref_media_code
> db14=>

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

Re: Changed functionality from 14.3 to 15.3

From
Erik Wienhold
Date:
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



Re: Changed functionality from 14.3 to 15.3

From
Michael Corey
Date:
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.

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

Re: Changed functionality from 14.3 to 15.3

From
Erik Wienhold
Date:
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



Re: Changed functionality from 14.3 to 15.3

From
Michael Corey
Date:
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@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

Re: Changed functionality from 14.3 to 15.3

From
Erik Wienhold
Date:
On 2023-09-20 15:19 -0400, Michael Corey wrote:
> 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 ?

Yes:

    db14=> select version();
                                                               version
        
 

-----------------------------------------------------------------------------------------------------------------------------
     PostgreSQL 14.3 (Debian 14.3-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1
20210110,64-bit
 
    (1 row)
    
    db14=> select current_user;
     current_user 
    --------------
     sten_schema
    (1 row)
    
    db14=> show search_path;
             search_path         
    -----------------------------
     "$user", ref_schema, public
    (1 row)
    
    db14=> select * from ref_media_code;
    ERROR:  permission denied for table ref_media_code
    db14=> select * from sten_media_codes_view ;
    ERROR:  permission denied for table ref_media_code

> If this is true then it seems to be something in our setup.

My guess is the missing

    GRANT SELECT ON ref_schema.ref_media_code TO sten_schema

unless that privilege should be inherited from some other role.

I think there are two possible situations if I don't mix up anything:

1. The setup script is flawed and tests should fail on both 14 and 15
   because of it, but something is foul on your 14 which results in a
   false positive (granted privileges on 14).

2. The setup script is ok and tests should pass but something is foul
   on your 15 which results in a false negative (missing privileges on
   15).

But besides that, tests depending on existing state (something that is
not part of each test setup) gives me the heebie-jeebies.  I worked on
a project were this was the case: Oracle databases for devs, test, and
QA copied from a bunch of blessed databases.  And somehow those copies
were incomplete sometimes, e.g. missing constraints or indexes.

> 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?

No, unless sten_schema is also member of ref_schema which is not the
case per your script.  Both roles are member of object_creator though.

> 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.

Can you create a 15 server from scratch and test it or do tests rely on
existing data?  You could dump and restore db14 from the original 14
into the new 15.  pg_dump covers privileges but not roles or memberships.
So you may be able to get rid of whatever may be wrong with your current
15.

But then again, I don't have an explanation why the upgrade 14 -> 15
would change privileges or roles.  The release notes for 15 list several
changes regarding roles and privileges but I don't see how they apply
here.

First item of E.5.3.1.6. Privileges [1] could be relevant:

"Allow table accesses done by a view to optionally be controlled by
 privileges of the view's caller.  Previously, view accesses were always
 treated as being done by the view's owner. That's still the default."

But view sten_media_codes_view is not defined with security_invoker=true
and sten_schema is current_user and owner.

[1] https://www.postgresql.org/docs/15/release-15.html#id-1.11.6.9.5.3.8

-- 
Erik



Re: Changed functionality from 14.3 to 15.3

From
Imre Samu
Date:
Michael Corey <michael.corey.ap@nielsen.com> ezt írta (időpont: 2023. szept. 20., Sze, 20:48):
... 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.  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.

Hi Michael,

I couldn't reproduce the outcome you observed in PG14.3 using the "postgres:14.3" (debian) Docker image. 

My minimal docker test:

docker pull postgres:14.3
docker run --name pg143tx -e POSTGRES_DB=db14 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=pw9 -d postgres:14.3
docker exec -ti pg143tx psql -d db14 -U postgres


My log:

psql (14.3 (Debian 14.3-1.pgdg110+1))
Type "help" for help.

....  < copy paste your test code > ....

CREATE ROLE
ERROR:  role "rds_superuser" does not exist
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
ALTER ROLE
ALTER ROLE
GRANT ROLE
CREATE ROLE
ALTER ROLE
ALTER ROLE
GRANT ROLE
CREATE SCHEMA
ALTER SCHEMA
GRANT
GRANT
GRANT
GRANT
CREATE TABLE
ALTER TABLE
GRANT
GRANT
GRANT
INSERT 0 1
INSERT 0 1
INSERT 0 1
WARNING:  there is no transaction in progress
COMMIT
CREATE SCHEMA
ALTER SCHEMA
GRANT
GRANT
GRANT
GRANT
CREATE VIEW
ALTER TABLE
GRANT
GRANT
db14=# \c db14 sten_schema
You are now connected to database "db14" as user "sten_schema".
db14=> select * from sten_media_codes_view ;
ERROR:  permission denied for table ref_media_code
db14=>  select * from ref_media_code ;
ERROR:  permission denied for table ref_media_code

db14=> SELECT version();
                                                           version                                                          
-----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.3 (Debian 14.3-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)

Regards,
  Imre

Re: Changed functionality from 14.3 to 15.3

From
"David G. Johnston"
Date:
On Wed, Sep 20, 2023 at 2:20 PM Erik Wienhold <ewie@ewie.name> wrote:
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.

Your description also suggests that maybe the v14 instance has altered default privileges setup that maybe the v15 doesn't have.

David J.

Re: Changed functionality from 14.3 to 15.3

From
Michael Corey
Date:
David,

How can I check the default privileges?  

On Wed, Sep 20, 2023 at 5:24 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, Sep 20, 2023 at 2:20 PM Erik Wienhold <ewie@ewie.name> wrote:
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.

Your description also suggests that maybe the v14 instance has altered default privileges setup that maybe the v15 doesn't have.

David J.



--
Michael Corey

Re: Changed functionality from 14.3 to 15.3

From
Michael Corey
Date:
Erik,

To make matters even more strange.  I checked the permissions of rds_superuser in 15 and 14

For 14
GRANT pg_monitor, pg_signal_backend, rds_password, rds_replication TO rds_superuser WITH ADMIN OPTION;

For 15
GRANT pg_checkpoint, pg_monitor, pg_read_all_data, pg_signal_backend, pg_write_all_data, rds_password, rds_replication TO rds_superuser WITH ADMIN OPTION;

AWS added these permissions, but based on what they do you would think this would allow the SELECTs in 15.

On Wed, Sep 20, 2023 at 4:40 PM Erik Wienhold <ewie@ewie.name> wrote:
On 2023-09-20 15:19 -0400, Michael Corey wrote:
> 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 ?

Yes:

        db14=> select version();
                                                                   version                                                           
        -----------------------------------------------------------------------------------------------------------------------------
         PostgreSQL 14.3 (Debian 14.3-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
        (1 row)

        db14=> select current_user;
         current_user
        --------------
         sten_schema
        (1 row)

        db14=> show search_path;
                 search_path         
        -----------------------------
         "$user", ref_schema, public
        (1 row)

        db14=> select * from ref_media_code;
        ERROR:  permission denied for table ref_media_code
        db14=> select * from sten_media_codes_view ;
        ERROR:  permission denied for table ref_media_code

> If this is true then it seems to be something in our setup.

My guess is the missing

        GRANT SELECT ON ref_schema.ref_media_code TO sten_schema

unless that privilege should be inherited from some other role.

I think there are two possible situations if I don't mix up anything:

1. The setup script is flawed and tests should fail on both 14 and 15
   because of it, but something is foul on your 14 which results in a
   false positive (granted privileges on 14).

2. The setup script is ok and tests should pass but something is foul
   on your 15 which results in a false negative (missing privileges on
   15).

But besides that, tests depending on existing state (something that is
not part of each test setup) gives me the heebie-jeebies.  I worked on
a project were this was the case: Oracle databases for devs, test, and
QA copied from a bunch of blessed databases.  And somehow those copies
were incomplete sometimes, e.g. missing constraints or indexes.

> 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?

No, unless sten_schema is also member of ref_schema which is not the
case per your script.  Both roles are member of object_creator though.

> 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.

Can you create a 15 server from scratch and test it or do tests rely on
existing data?  You could dump and restore db14 from the original 14
into the new 15.  pg_dump covers privileges but not roles or memberships.
So you may be able to get rid of whatever may be wrong with your current
15.

But then again, I don't have an explanation why the upgrade 14 -> 15
would change privileges or roles.  The release notes for 15 list several
changes regarding roles and privileges but I don't see how they apply
here.

First item of E.5.3.1.6. Privileges [1] could be relevant:

"Allow table accesses done by a view to optionally be controlled by
 privileges of the view's caller.  Previously, view accesses were always
 treated as being done by the view's owner. That's still the default."

But view sten_media_codes_view is not defined with security_invoker=true
and sten_schema is current_user and owner.

[1] https://www.postgresql.org/docs/15/release-15.html#id-1.11.6.9.5.3.8

--
Erik


--
Michael Corey

Re: Changed functionality from 14.3 to 15.3

From
"David G. Johnston"
Date:
On Wed, Sep 20, 2023 at 2:48 PM Michael Corey <michael.corey.ap@nielsen.com> wrote:
How can I check the default privileges?  

\ddp


David J.

Re: Changed functionality from 14.3 to 15.3

From
Erik Wienhold
Date:
On 2023-09-20 14:24 -0700, David G. Johnston wrote:
> On Wed, Sep 20, 2023 at 2:20 PM Erik Wienhold <ewie@ewie.name> wrote:
> 
> > 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.
> >
> 
> Your description also suggests that maybe the v14 instance has altered
> default privileges setup that maybe the v15 doesn't have.

Not possible for the roles created in the setup script because the
grantee must exist when defining default privileges.  Also \dp shows
only those privileges granted in the setup script.

More questions that need answers:

* How are the databases created?
* Does the template database contribute anything, e.g. event triggers?
* Any other setup scripts involved?

-- 
Erik



Re: Changed functionality from 14.3 to 15.3

From
Erik Wienhold
Date:
On 2023-09-20 17:53 -0400, Michael Corey wrote:
> To make matters even more strange.  I checked the permissions of
> rds_superuser in 15 and 14
> 
> For 14
> GRANT pg_monitor, pg_signal_backend, rds_password, rds_replication TO
> rds_superuser WITH ADMIN OPTION;
> 
> For 15
> GRANT pg_checkpoint, pg_monitor, *pg_read_all_data*, pg_signal_backend,
> *pg_write_all_data*, rds_password, rds_replication TO rds_superuser WITH
> ADMIN OPTION;
> 
> AWS added these permissions, but based on what they do you would think this
> would allow the SELECTs in 15.

Yes it would if sten_schema would inherit from rds_superuser.  But it
cannot inherit privileges from rds_superuser (indrect membership through
object_creator) because object_creator was created with NOINHERIT.  And
INHERIT applies to direct memberships only.

-- 
Erik



Re: Changed functionality from 14.3 to 15.3

From
Laurenz Albe
Date:
On Wed, 2023-09-20 at 14:24 -0700, David G. Johnston wrote:
> On Wed, Sep 20, 2023 at 2:20 PM Erik Wienhold <ewie@ewie.name> wrote:
> > 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.
>
> Your description also suggests that maybe the v14 instance has altered
> default privileges setup that maybe the v15 doesn't have.

Since there were references to "rds_superuser", it could also be that this
is caused by modifications that Amazon did to PostgreSQL.

Yours,
Laurenz Albe



Re: Changed functionality from 14.3 to 15.3

From
Michael Corey
Date:
I created a clean 14.3 server with everything default on server creation.  Ran the setup script did the test and again I was able to query the data successfully.  I then decided to create a clean 15.3 server with everything default.  Ran the setup script did the test and was not able to query the data.

Interestingly enough I contacted AWS and presented the same issue to them and they informed me that they could duplicate my exact issue, and said yes there was a functionality change from 14 to 15, but they did not say if the change was something they did with their RDS Postgres or was it something changed in the underlying Postgres build.



On Wed, Sep 20, 2023 at 7:11 PM Erik Wienhold <ewie@ewie.name> wrote:
On 2023-09-20 17:53 -0400, Michael Corey wrote:
> To make matters even more strange.  I checked the permissions of
> rds_superuser in 15 and 14
>
> For 14
> GRANT pg_monitor, pg_signal_backend, rds_password, rds_replication TO
> rds_superuser WITH ADMIN OPTION;
>
> For 15
> GRANT pg_checkpoint, pg_monitor, *pg_read_all_data*, pg_signal_backend,
> *pg_write_all_data*, rds_password, rds_replication TO rds_superuser WITH
> ADMIN OPTION;
>
> AWS added these permissions, but based on what they do you would think this
> would allow the SELECTs in 15.

Yes it would if sten_schema would inherit from rds_superuser.  But it
cannot inherit privileges from rds_superuser (indrect membership through
object_creator) because object_creator was created with NOINHERIT.  And
INHERIT applies to direct memberships only.

--
Erik


--
Michael Corey