On Monday, 8 June 2020 12:42, Paul Förster <paul.foerster@gmail.com> wrote:
> Hi Laura,
>
> > On 08. Jun, 2020, at 12:46, Laura Smith n5d9xq3ti233xiyif2vp@protonmail.ch I had a lightbulb moment just now and
triedthat, but it doesn't seem to be working.
> > The app returns "pg_execute(): Query failed: ERROR: permission denied for table...."
> > This is despite me:
> > • Changing to SECURITY INVOKER on the PG function.
> > • Granting the app user relevant perms on the underlying table
> > • Re-granting execute for the app on the function
> > Am I missing somehthing ?
>
> another possibility maybe is to use session_user instead of current_user in your policy.
>
> current_user name user name of current execution context
> session_user name session user name
>
> The latter is the name of the user who actually started the session. So it should be myappuser in your case.
>
> https://www.postgresql.org/docs/current/functions-info.html
>
> Cheers,
> Paul
Thanks Paul, will experiment with session_user.
But actually I found the solution, the function I was testing was using "INSERT ON CONFLICT UPDATE". And it seems that
requiresSELECT permissions due to "ON CONFLICT" (appuser was previously only granted INSERT and UPDATE).
