Postgres 12 RLS

From: Laura Smith <n5d9xq3ti233xiyif2vp(at)protonmail(dot)ch>
To: postgre <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Postgres 12 RLS
Date: 2020-06-08 10:17:07
Message-ID: HQQTmWw2GOI540iQNVYlo-Hdq7FXd-L1Iyq1g3SOen8t-jugIBcwQ1FCKngTRnZ4wwttn1z5mMLbQrS5aK_srLa6HKN6mFUOoiUDtS__y0I=@protonmail.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 thouhg the 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)))

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Paul Förster 2020-06-08 10:25:04 Re: Postgres 12 RLS
Previous Message Daniel Verite 2020-06-08 10:07:53 Re: Concurrenctly running CREATE TEMP TABLE IF NOT EXISTS [...] AS [...]