Hi Adrian, thanks for responding.
How would I restrict access to the SECURITY DEFINER function? If it can be called by the trigger, it can be called by the user as well I would think. Same issue as access to the table itself only now with a superuser intermediary, right?
On Tue, Jun 18, 2019 at 6:20 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 6/18/19 10:14 AM, Miles Elam wrote:
> Thanks for the suggestion. Unfortunately we only have a single login
> role (it's a web app) and then we SET ROLE according to the contents of
> a JSON Web Token. So we end up with SESSION_USER as the logged in user
> and the active role as CURRENT_USER.
Have not tried it but nested function?:
1) Outer function runs as normal user and grabs the CURRENT_USER. This
is passed into 2)
2) Audit function that runs with SECURITY DEFINER.
Other option is to record the CURRENT_USER in the table the trigger is
on and just pass that to the audit function.
>
> It may be that we're just stuck with a gap and need to just try and keep
> track of our mutation points, such as limit what is accessible through
> REST or GraphQL, and there is no way to fundamentally lock this down in
> Postgres. I was checking the mailing list to see if I'd missed anything.
>
>
> On Tue, Jun 18, 2019 at 9:47 AM Torsten Förtsch <tfoertsch123@xxxxxxxxx
> <mailto:tfoertsch123@xxxxxxxxx>> wrote:
>
> Have you tried session_user?
>
> create function xx() returns table (cur text, sess text)
> security definer language sql as $$
> select current_user::text, session_user::text;
> $$;
>
> Then log in as different user and:
>
> => select (xx()).*;
> cur | sess
> ----------+-------
> postgres | write
>
>
> On Tue, Jun 18, 2019 at 6:30 PM Miles Elam
> <miles.elam@xxxxxxxxxxxxxx <mailto:miles.elam@xxxxxxxxxxxxxx>> wrote:
>
> That seems straightforward. Unfortunately I also want to know
> the user/role that performed the operation. If I use SECURITY
> DEFINER, I get the superuser account back from CURRENT_USER, not
> the actual user.
>
> Sorry, should have included that in the original email. How do I
> restrict access while still retaining info about the current
> user/role?
>
>
> On Mon, Jun 17, 2019 at 5:47 PM <raf@xxxxxxx
> <mailto:raf@xxxxxxx>> wrote:
>
> Adrian Klaver wrote:
>
> > On 6/17/19 4:54 PM, Miles Elam wrote:
> > > Is there are way to restrict direct access to a table
> for inserts but
> > > allow a trigger on another table to perform an insert
> for that user?
> > >
> > > I'm trying to implement an audit table without allowing
> user tampering
> > > with the audit information.
> >
> > Would the below not work?:
> > CREATE the table as superuser or other privileged user
> > Have trigger function run as above user(use SECURITY DEFINER)
>
> and make sure not to give any other users insert/update/delete
> permissions on the audit table.
>
> > > Thanks in advance,
> > >
> > > Miles Elam
> >
> > --
> > Adrian Klaver
> > adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
>
>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
