Re: Inserts restricted to a trigger

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(dot)klaver(at)aklaver(dot)com>
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(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
>