Re: Changed functionality from 14.3 to 15.3 - Mailing list pgsql-general

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