Re: Permission inconsistency with views that call functions

On 2/22/21 10:32 PM, David Wheeler wrote:
> I’m seeing some inconsistency with how permissions are enforced within views. In
> particular, if the view accesses a table directly, then the table is accessible,
> however if the view uses a function to access the table then permission is denied.

Without looking too closely at the details, I can almost guarantee that the
issue is that FUNCTIONs default to "SECURITY INVOKER" whereas VIEWs operate as
if they were "SECURITY DEFINER". See slide 33 here:

http://joeconway.com/presentations/security-pgcon2020.pdf

The solution to your issue is possibly to make the function "SECURITY DEFINER".

I have mused previously (not sure if I ever did on the lists, but in any case)
that it would be cool if VIEWs could have the option to be either DEFINER or
INVOKER so that VIEWs and FUNCTIONs could be treated the same, but no efforts
have been made in that direction as far as I am aware.

HTH,

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development