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
