Re: Inserts restricted to a trigger

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(at)gmail(dot)com
> <mailto:tfoertsch123(at)gmail(dot)com>> 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(dot)elam(at)productops(dot)com <mailto:miles(dot)elam(at)productops(dot)com>> 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(at)raf(dot)org
> <mailto:raf(at)raf(dot)org>> 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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com