... 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.
Hi Michael,
I couldn't reproduce the outcome you observed in PG14.3 using the "postgres:14.3" (debian) Docker image.
My minimal docker test:
docker pull postgres:14.3
docker run --name pg143tx -e POSTGRES_DB=db14 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=pw9 -d postgres:14.3
docker exec -ti pg143tx psql -d db14 -U postgres
My log:
psql (14.3 (Debian 14.3-1.pgdg110+1))
Type "help" for help.
.... < copy paste your test code > ....
CREATE ROLE
ERROR: role "rds_superuser" does not exist
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
ALTER ROLE
ALTER ROLE
GRANT ROLE
CREATE ROLE
ALTER ROLE
ALTER ROLE
GRANT ROLE
CREATE SCHEMA
ALTER SCHEMA
GRANT
GRANT
GRANT
GRANT
CREATE TABLE
ALTER TABLE
GRANT
GRANT
GRANT
INSERT 0 1
INSERT 0 1
INSERT 0 1
WARNING: there is no transaction in progress
COMMIT
CREATE SCHEMA
ALTER SCHEMA
GRANT
GRANT
GRANT
GRANT
CREATE VIEW
ALTER TABLE
GRANT
GRANT
db14=# \c db14 sten_schema
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=> 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)
Regards,
Imre