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@gmail.com
> <mailto:tfoertsch123@gmail.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.elam@productops.com <mailto:miles.elam@productops.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@raf.org
>         <mailto:raf@raf.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com