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(at)ewie(dot)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