Re: Postgres 12 RLS

On Monday, 8 June 2020 11:25, Paul Förster <paul(dot)foerster(at)gmail(dot)com> wrote:

> Hi Laura,
>
> > On 08. Jun, 2020, at 12:17, Laura Smith n5d9xq3ti233xiyif2vp(at)protonmail(dot)ch $$ LANGUAGE plpgsql SECURITY DEFINER;
>
> you might want to use security invoker instead of definer.
>
> https://www.postgresql.org/docs/current/sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY
>
> https://www.cybertec-postgresql.com/en/abusing-security-definer-functions/
>
> Cheers,
> Paul

Hi Paul,

I had a lightbulb moment just now and tried that, 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 ?