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.kla...@aklaver.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 <tfoertsch...@gmail.com > > <mailto:tfoertsch...@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.e...@productops.com <mailto:miles.e...@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 <r...@raf.org > > <mailto:r...@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.kla...@aklaver.com <mailto: > adrian.kla...@aklaver.com> > > > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
