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