Changed functionality from 14.3 to 15.3

Started by Michael Coreyover 2 years ago17 messagesgeneral

michael.corey.ap@nielsen.com

over 2 years ago

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

Erik Wienhold

ewie@ewie.name

over 2 years ago

In reply to: Michael Corey (#1)

Re: Changed functionality from 14.3 to 15.3

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

michael.corey.ap@nielsen.com

over 2 years ago

In reply to: Erik Wienhold (#2)

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@ewie.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

Erik Wienhold

ewie@ewie.name

over 2 years ago

In reply to: Michael Corey (#3)

Re: Changed functionality from 14.3 to 15.3

On 2023-09-20 09:15 -0400, Michael Corey wrote:

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.

And both databases start empty or at least in the exact same state?

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.

I don't know why those privileges could be missing if that script
completes. Please provide the privileges like I asked to see what's
actually granted on both databases:

On Tue, Sep 19, 2023 at 8:17 PM Erik Wienhold <ewie@ewie.name> wrote:

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

michael.corey.ap@nielsen.com

over 2 years ago

In reply to: Erik Wienhold (#4)

Re: Changed functionality from 14.3 to 15.3

"And both databases start empty or at least in the exact same state?"
Yes, this is a test case, so I created two new databases one in 14.3 and
one in 15.3, did the setup as I provided, and ran the two SELECTs in both
databases and received different results.

On Wed, Sep 20, 2023 at 12:33 PM Erik Wienhold <ewie@ewie.name> wrote:

On 2023-09-20 09:15 -0400, Michael Corey wrote:

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.

And both databases start empty or at least in the exact same state?

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.

I don't know why those privileges could be missing if that script
completes. Please provide the privileges like I asked to see what's
actually granted on both databases:

On Tue, Sep 19, 2023 at 8:17 PM Erik Wienhold <ewie@ewie.name> wrote:

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

Erik Wienhold

ewie@ewie.name

over 2 years ago

In reply to: Michael Corey (#5)

Re: Changed functionality from 14.3 to 15.3

On 2023-09-20 13:17 -0400, Michael Corey wrote:

PG 14 Server
psql (14.2, server 14.3)
You are now connected to database "db14" as user "postgres".
db14=> \dn+ ref_schema|sten_schema
List of schemas
Name | Owner | Access privileges | Description
-------------+-------------+---------------------------------+-------------
ref_schema | ref_schema | ref_schema=UC/ref_schema +|
| | sten_schema=U/ref_schema +|
| | ref_schema_read=U/ref_schema +|
| | ref_schema_write=U/ref_schema |
sten_schema | sten_schema | sten_schema=UC/sten_schema +|
| | ref_schema=U/sten_schema +|
| | sten_schema_read=U/sten_schema +|
| | sten_schema_write=U/sten_schema |
(2 rows)

db14=> \dp ref_schema.ref_media_code
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
------------+----------------+-------+--------------------------------+-------------------+----------
ref_schema | ref_media_code | table | ref_schema=arwdDxt/ref_schema +| |
| | | ref_schema_read=r/ref_schema +| |
| | | sten_schema_write=r/ref_schema | |
(1 row)

db14=> \dp sten_schema.sten_media_codes_view
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
-------------+-----------------------+------+---------------------------------+-------------------+----------
sten_schema | sten_media_codes_view | view | sten_schema=arwdDxt/sten_schema+| |
| | | sten_schema_write=r/sten_schema | |
(1 row)

PG 15 server
psql (14.2, server 15.3)
You are now connected to database "db14" as user "postgres".
db14=> \dn+ ref_schema|sten_schema
List of schemas
Name | Owner | Access privileges | Description
-------------+-------------+---------------------------------+-------------
ref_schema | ref_schema | ref_schema=UC/ref_schema +|
| | sten_schema=U/ref_schema +|
| | ref_schema_read=U/ref_schema +|
| | ref_schema_write=U/ref_schema |
sten_schema | sten_schema | sten_schema=UC/sten_schema +|
| | ref_schema=U/sten_schema +|
| | sten_schema_read=U/sten_schema +|
| | sten_schema_write=U/sten_schema |
(2 rows)

db14=> \dp ref_schema.ref_media_code
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
------------+----------------+-------+--------------------------------+-------------------+----------
ref_schema | ref_media_code | table | ref_schema=arwdDxt/ref_schema +| |
| | | ref_schema_read=r/ref_schema +| |
| | | sten_schema_write=r/ref_schema | |
(1 row)

db14=> \dp sten_schema.sten_media_codes_view
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
-------------+-----------------------+------+---------------------------------+-------------------+----------
sten_schema | sten_media_codes_view | view | sten_schema=arwdDxt/sten_schema+| |
| | | sten_schema_write=r/sten_schema | |
(1 row)

Thanks. Those privileges are identical. But sten_schema has no SELECT
privilege on table ref_media_code on either server. That's necessary
when querying through view sten_media_codes_view. And there's also no
GRANT for that in your script. I somehow missed that previously.

"And both databases start empty or at least in the exact same state?"
Yes, this is a test case, so I created two new databases one in 14.3 and
one in 15.3, did the setup as I provided, and ran the two SELECTs in both
databases and received different results.

Now I had the time to run your script and I can reproduce the missing
privileges on both 14.3 and 15.3.

Has your 14.3 some left-over state from previous test runs? I assume
the server is not re-created for each test run. I was wondering if the
roles may still exist and with additional memberships. But then again
the script just uses CREATE ROLE. So the roles definitely do not exist
beforehand. But what are the actual memberships of sten_schema?
Because it must inherit SELECT on ref_media_code on 14.3. It can't be
from object_creator because that role also gets newly created.

--
Erik

Michael Corey

michael.corey.ap@nielsen.com

over 2 years ago

In reply to: Erik Wienhold (#6)

Re: Changed functionality from 14.3 to 15.3

Erik,

Just to be clear in your last response are you saying on your 14.3 you are
getting the
ERROR: permission denied for table ref_media_code ?

If this is true then it seems to be something in our setup. This database
may have been upgraded from 13.x to 14. The sten_schema has INHERIT when I
create, but that does not mean INHERIT from ref_schema, correct?

All the items I have created just once I have not removed or recreated any
of these for my test. The problem is impacting my real actual schemas and
was discovered after we did the upgrade to 15. I decided then to restore
the original 14 server and made two copies. I kept one as 14 and upgraded
the other to 15. Lastly, I created the test case.

On Wed, Sep 20, 2023 at 3:07 PM Erik Wienhold <ewie@ewie.name> wrote:

On 2023-09-20 13:17 -0400, Michael Corey wrote:

PG 14 Server
psql (14.2, server 14.3)
You are now connected to database "db14" as user "postgres".
db14=> \dn+ ref_schema|sten_schema
List of schemas
Name | Owner | Access privileges |

Description

-------------+-------------+---------------------------------+-------------

ref_schema | ref_schema | ref_schema=UC/ref_schema +|
| | sten_schema=U/ref_schema +|
| | ref_schema_read=U/ref_schema +|
| | ref_schema_write=U/ref_schema |
sten_schema | sten_schema | sten_schema=UC/sten_schema +|
| | ref_schema=U/sten_schema +|
| | sten_schema_read=U/sten_schema +|
| | sten_schema_write=U/sten_schema |
(2 rows)

db14=> \dp ref_schema.ref_media_code
Access privileges
Schema | Name | Type | Access privileges |

Column privileges | Policies

------------+----------------+-------+--------------------------------+-------------------+----------

ref_schema | ref_media_code | table | ref_schema=arwdDxt/ref_schema +|

|

| | | ref_schema_read=r/ref_schema +|

|

| | | sten_schema_write=r/ref_schema |

|

(1 row)

db14=> \dp sten_schema.sten_media_codes_view
Access privileges
Schema | Name | Type | Access privileges

| Column privileges | Policies

-------------+-----------------------+------+---------------------------------+-------------------+----------

sten_schema | sten_media_codes_view | view |

sten_schema=arwdDxt/sten_schema+| |

| | |

sten_schema_write=r/sten_schema | |

(1 row)

PG 15 server
psql (14.2, server 15.3)
You are now connected to database "db14" as user "postgres".
db14=> \dn+ ref_schema|sten_schema
List of schemas
Name | Owner | Access privileges |

Description

-------------+-------------+---------------------------------+-------------

ref_schema | ref_schema | ref_schema=UC/ref_schema +|
| | sten_schema=U/ref_schema +|
| | ref_schema_read=U/ref_schema +|
| | ref_schema_write=U/ref_schema |
sten_schema | sten_schema | sten_schema=UC/sten_schema +|
| | ref_schema=U/sten_schema +|
| | sten_schema_read=U/sten_schema +|
| | sten_schema_write=U/sten_schema |
(2 rows)

db14=> \dp ref_schema.ref_media_code
Access privileges
Schema | Name | Type | Access privileges |

Column privileges | Policies

------------+----------------+-------+--------------------------------+-------------------+----------

ref_schema | ref_media_code | table | ref_schema=arwdDxt/ref_schema +|

|

| | | ref_schema_read=r/ref_schema +|

|

| | | sten_schema_write=r/ref_schema |

|

(1 row)

db14=> \dp sten_schema.sten_media_codes_view
Access privileges
Schema | Name | Type | Access privileges

| Column privileges | Policies

-------------+-----------------------+------+---------------------------------+-------------------+----------

sten_schema | sten_media_codes_view | view |

sten_schema=arwdDxt/sten_schema+| |

| | |

sten_schema_write=r/sten_schema | |

(1 row)

Thanks. Those privileges are identical. But sten_schema has no SELECT
privilege on table ref_media_code on either server. That's necessary
when querying through view sten_media_codes_view. And there's also no
GRANT for that in your script. I somehow missed that previously.

"And both databases start empty or at least in the exact same state?"
Yes, this is a test case, so I created two new databases one in 14.3 and
one in 15.3, did the setup as I provided, and ran the two SELECTs in both
databases and received different results.

Now I had the time to run your script and I can reproduce the missing
privileges on both 14.3 and 15.3.

Has your 14.3 some left-over state from previous test runs? I assume
the server is not re-created for each test run. I was wondering if the
roles may still exist and with additional memberships. But then again
the script just uses CREATE ROLE. So the roles definitely do not exist
beforehand. But what are the actual memberships of sten_schema?
Because it must inherit SELECT on ref_media_code on 14.3. It can't be
from object_creator because that role also gets newly created.

--
Erik

--
Michael Corey

Erik Wienhold

ewie@ewie.name

over 2 years ago

In reply to: Michael Corey (#7)

Re: Changed functionality from 14.3 to 15.3

On 2023-09-20 15:19 -0400, Michael Corey wrote:

Just to be clear in your last response are you saying on your 14.3 you are
getting the
ERROR: permission denied for table ref_media_code ?

Yes:

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)

db14=> select current_user;
current_user
--------------
sten_schema
(1 row)

db14=> show search_path;
search_path
-----------------------------
"$user", ref_schema, public
(1 row)

db14=> select * from ref_media_code;
ERROR: permission denied for table ref_media_code
db14=> select * from sten_media_codes_view ;
ERROR: permission denied for table ref_media_code

If this is true then it seems to be something in our setup.

My guess is the missing

GRANT SELECT ON ref_schema.ref_media_code TO sten_schema

unless that privilege should be inherited from some other role.

I think there are two possible situations if I don't mix up anything:

1. The setup script is flawed and tests should fail on both 14 and 15
because of it, but something is foul on your 14 which results in a
false positive (granted privileges on 14).

2. The setup script is ok and tests should pass but something is foul
on your 15 which results in a false negative (missing privileges on
15).

But besides that, tests depending on existing state (something that is
not part of each test setup) gives me the heebie-jeebies. I worked on
a project were this was the case: Oracle databases for devs, test, and
QA copied from a bunch of blessed databases. And somehow those copies
were incomplete sometimes, e.g. missing constraints or indexes.

This database may have been upgraded from 13.x to 14. The sten_schema
has INHERIT when I create, but that does not mean INHERIT from
ref_schema, correct?

No, unless sten_schema is also member of ref_schema which is not the
case per your script. Both roles are member of object_creator though.

All the items I have created just once I have not removed or recreated any
of these for my test. The problem is impacting my real actual schemas and
was discovered after we did the upgrade to 15. I decided then to restore
the original 14 server and made two copies. I kept one as 14 and upgraded
the other to 15. Lastly, I created the test case.

Can you create a 15 server from scratch and test it or do tests rely on
existing data? You could dump and restore db14 from the original 14
into the new 15. pg_dump covers privileges but not roles or memberships.
So you may be able to get rid of whatever may be wrong with your current
15.

But then again, I don't have an explanation why the upgrade 14 -> 15
would change privileges or roles. The release notes for 15 list several
changes regarding roles and privileges but I don't see how they apply
here.

First item of E.5.3.1.6. Privileges [1]https://www.postgresql.org/docs/15/release-15.html#id-1.11.6.9.5.3.8 could be relevant:

"Allow table accesses done by a view to optionally be controlled by
privileges of the view's caller. Previously, view accesses were always
treated as being done by the view's owner. That's still the default."

But view sten_media_codes_view is not defined with security_invoker=true
and sten_schema is current_user and owner.

[1]: https://www.postgresql.org/docs/15/release-15.html#id-1.11.6.9.5.3.8

--
Erik

Imre Samu

pella.samu@gmail.com

over 2 years ago

In reply to: Michael Corey (#3)

Re: Changed functionality from 14.3 to 15.3

Michael Corey <michael.corey.ap@nielsen.com> ezt írta (időpont: 2023.
szept. 20., Sze, 20:48):

... 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

#10

David G. Johnston

david.g.johnston@gmail.com

over 2 years ago

In reply to: Erik Wienhold (#6)

Re: Changed functionality from 14.3 to 15.3

On Wed, Sep 20, 2023 at 2:20 PM Erik Wienhold <ewie@ewie.name> wrote:

Has your 14.3 some left-over state from previous test runs? I assume
the server is not re-created for each test run. I was wondering if the
roles may still exist and with additional memberships. But then again
the script just uses CREATE ROLE. So the roles definitely do not exist
beforehand. But what are the actual memberships of sten_schema?
Because it must inherit SELECT on ref_media_code on 14.3. It can't be
from object_creator because that role also gets newly created.

Your description also suggests that maybe the v14 instance has altered
default privileges setup that maybe the v15 doesn't have.

David J.

#11

Michael Corey

michael.corey.ap@nielsen.com

over 2 years ago

In reply to: David G. Johnston (#10)

Re: Changed functionality from 14.3 to 15.3

David,

How can I check the default privileges?

On Wed, Sep 20, 2023 at 5:24 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Wed, Sep 20, 2023 at 2:20 PM Erik Wienhold <ewie@ewie.name> wrote:

Has your 14.3 some left-over state from previous test runs? I assume
the server is not re-created for each test run. I was wondering if the
roles may still exist and with additional memberships. But then again
the script just uses CREATE ROLE. So the roles definitely do not exist
beforehand. But what are the actual memberships of sten_schema?
Because it must inherit SELECT on ref_media_code on 14.3. It can't be
from object_creator because that role also gets newly created.

Your description also suggests that maybe the v14 instance has altered
default privileges setup that maybe the v15 doesn't have.

David J.

--
Michael Corey

#12

Michael Corey

michael.corey.ap@nielsen.com

over 2 years ago

In reply to: Erik Wienhold (#8)

Re: Changed functionality from 14.3 to 15.3

Erik,

To make matters even more strange. I checked the permissions of
rds_superuser in 15 and 14

For 14
GRANT pg_monitor, pg_signal_backend, rds_password, rds_replication TO
rds_superuser WITH ADMIN OPTION;

For 15
GRANT pg_checkpoint, pg_monitor, *pg_read_all_data*, pg_signal_backend,
*pg_write_all_data*, rds_password, rds_replication TO rds_superuser WITH
ADMIN OPTION;

AWS added these permissions, but based on what they do you would think this
would allow the SELECTs in 15.

On Wed, Sep 20, 2023 at 4:40 PM Erik Wienhold <ewie@ewie.name> wrote:

On 2023-09-20 15:19 -0400, Michael Corey wrote:

Just to be clear in your last response are you saying on your 14.3 you

are

getting the
ERROR: permission denied for table ref_media_code ?

Yes:

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)

db14=> select current_user;
current_user
--------------
sten_schema
(1 row)

db14=> show search_path;
search_path
-----------------------------
"$user", ref_schema, public
(1 row)

db14=> select * from ref_media_code;
ERROR: permission denied for table ref_media_code
db14=> select * from sten_media_codes_view ;
ERROR: permission denied for table ref_media_code

If this is true then it seems to be something in our setup.

My guess is the missing

GRANT SELECT ON ref_schema.ref_media_code TO sten_schema

unless that privilege should be inherited from some other role.

I think there are two possible situations if I don't mix up anything:

1. The setup script is flawed and tests should fail on both 14 and 15
because of it, but something is foul on your 14 which results in a
false positive (granted privileges on 14).

2. The setup script is ok and tests should pass but something is foul
on your 15 which results in a false negative (missing privileges on
15).

But besides that, tests depending on existing state (something that is
not part of each test setup) gives me the heebie-jeebies. I worked on
a project were this was the case: Oracle databases for devs, test, and
QA copied from a bunch of blessed databases. And somehow those copies
were incomplete sometimes, e.g. missing constraints or indexes.

This database may have been upgraded from 13.x to 14. The sten_schema
has INHERIT when I create, but that does not mean INHERIT from
ref_schema, correct?

No, unless sten_schema is also member of ref_schema which is not the
case per your script. Both roles are member of object_creator though.

All the items I have created just once I have not removed or recreated

any

of these for my test. The problem is impacting my real actual schemas

and

was discovered after we did the upgrade to 15. I decided then to restore
the original 14 server and made two copies. I kept one as 14 and upgraded
the other to 15. Lastly, I created the test case.

Can you create a 15 server from scratch and test it or do tests rely on
existing data? You could dump and restore db14 from the original 14
into the new 15. pg_dump covers privileges but not roles or memberships.
So you may be able to get rid of whatever may be wrong with your current
15.

But then again, I don't have an explanation why the upgrade 14 -> 15
would change privileges or roles. The release notes for 15 list several
changes regarding roles and privileges but I don't see how they apply
here.

First item of E.5.3.1.6. Privileges [1] could be relevant:

"Allow table accesses done by a view to optionally be controlled by
privileges of the view's caller. Previously, view accesses were always
treated as being done by the view's owner. That's still the default."

But view sten_media_codes_view is not defined with security_invoker=true
and sten_schema is current_user and owner.

[1] https://www.postgresql.org/docs/15/release-15.html#id-1.11.6.9.5.3.8

--
Erik

--
Michael Corey

#13

David G. Johnston

david.g.johnston@gmail.com

over 2 years ago

In reply to: Michael Corey (#11)

Re: Changed functionality from 14.3 to 15.3

On Wed, Sep 20, 2023 at 2:48 PM Michael Corey <michael.corey.ap@nielsen.com>
wrote:

How can I check the default privileges?

\ddp

https://www.postgresql.org/docs/current/catalog-pg-default-acl.html

David J.

#14

Erik Wienhold

ewie@ewie.name

over 2 years ago

In reply to: David G. Johnston (#10)

Re: Changed functionality from 14.3 to 15.3

On 2023-09-20 14:24 -0700, David G. Johnston wrote:

On Wed, Sep 20, 2023 at 2:20 PM Erik Wienhold <ewie@ewie.name> wrote:

Has your 14.3 some left-over state from previous test runs? I assume
the server is not re-created for each test run. I was wondering if the
roles may still exist and with additional memberships. But then again
the script just uses CREATE ROLE. So the roles definitely do not exist
beforehand. But what are the actual memberships of sten_schema?
Because it must inherit SELECT on ref_media_code on 14.3. It can't be
from object_creator because that role also gets newly created.

Your description also suggests that maybe the v14 instance has altered
default privileges setup that maybe the v15 doesn't have.

Not possible for the roles created in the setup script because the
grantee must exist when defining default privileges. Also \dp shows
only those privileges granted in the setup script.