Обсуждение: Postgres 12 RLS

Hi,

I'm having a little trouble with RLS in Postgres 12, although first time I've used RLS, so it might just be me !

The problem is that I am calling a function from a web-app, but the function seems to be executing as "postgres" even
thouhgthe web-app logs in as a completely different role ? 

This means that current_user in the function resolves to "postgres" instead of the app user.

This is an example of a function :
create function addses(p_regid text,p_msgid text,p_reqid text) returns integer AS $$
BEGIN
UPDATE foo_regs set reg_aws_ses_msgid=p_msgid,reg_aws_amzn_requestid=p_reqid where uuid=p_regid;
        IF FOUND THEN
                return 1;
        ELSE
                return 0;
        END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
grant execute on function addses(p_regid text,p_msgid text,p_reqid text) to myappuser;

The foo_regs table has the following RLS policy:
Policies:
    POLICY "foo_regs_policy"
      USING (((event_id = CURRENT_USER) AND (reg_hide IS FALSE)))

Hi Laura,

> On 08. Jun, 2020, at 12:17, Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.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

On Monday, 8 June 2020 11:25, Paul Förster <paul.foerster@gmail.com> wrote:

> Hi Laura,
>
> > On 08. Jun, 2020, at 12:17, Laura Smith n5d9xq3ti233xiyif2vp@protonmail.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 ?

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

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).