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
