Re: Inserts restricted to a trigger
От | Adrian Klaver |
---|---|
Тема | Re: Inserts restricted to a trigger |
Дата | |
Msg-id | b5b505ba-e9e6-98ee-a2b7-57d5d51bae09@aklaver.com обсуждение исходный текст |
Ответ на | Re: Inserts restricted to a trigger (Miles Elam <miles.elam@productops.com>) |
Ответы |
Re: Inserts restricted to a trigger
(Miles Elam <miles.elam@productops.com>)
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: