From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Miles Elam <miles(dot)elam(at)productops(dot)com>, pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Inserts restricted to a trigger |
Date: | 2019-06-19 01:20:11 |
Message-ID: | b5b505ba-e9e6-98ee-a2b7-57d5d51bae09@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(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
From | Date | Subject | |
---|---|---|---|
Next Message | Achilleas Mantzios | 2019-06-19 08:46:43 | Re: unexpected behavior with pglogical -- bug? |
Previous Message | Adrian Klaver | 2019-06-19 01:03:18 | Re: psql UPDATE field [tab] expands to DEFAULT? |