Re: Changed functionality from 14.3 to 15.3

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