Changed functionality from 14.3 to 15.3 - Mailing list pgsql-general

From Michael Corey
Subject Changed functionality from 14.3 to 15.3
Date
Msg-id CAABu8T85mYLy1is3-EmK1FgKKpuFOXQUSM90UazNV3PjOsUGig@mail.gmail.com
Whole thread Raw
Responses Re: Changed functionality from 14.3 to 15.3
List pgsql-general
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

pgsql-general by date:

Previous
From: Anthony Apollis
Date:
Subject: Calculating Days/Time(Are Loops Neccessary?)
Next
From: Erik Wienhold
Date:
Subject: Re: Changed functionality from 14.3 to 15.3