Обсуждение: Changed functionality from 14.3 to 15.3

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

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

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

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

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

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

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