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@xxxxxxxxx> 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
