Search Postgresql Archives

Re: Inserts restricted to a trigger

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 6/19/19 3:07 PM, Miles Elam wrote:
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?

We may need to back this up:

1) What is the audit table recording?

2) How much access do your users have to the database itself, outside the Web app?

3) What access do users have in the Web app?






On Tue, Jun 18, 2019 at 6:20 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxx <mailto:tfoertsch123@xxxxxxxxx>
     > <mailto:tfoertsch123@xxxxxxxxx <mailto:tfoertsch123@xxxxxxxxx>>>
    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@xxxxxxxxxxxxxx <mailto:miles.elam@xxxxxxxxxxxxxx>
    <mailto:miles.elam@xxxxxxxxxxxxxx
    <mailto:miles.elam@xxxxxxxxxxxxxx>>> 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@xxxxxxx
    <mailto:raf@xxxxxxx>
     >         <mailto:raf@xxxxxxx <mailto:raf@xxxxxxx>>> 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@xxxxxxxxxxx
    <mailto:adrian.klaver@xxxxxxxxxxx> <mailto:adrian.klaver@xxxxxxxxxxx
    <mailto:adrian.klaver@xxxxxxxxxxx>>
     >
     >


-- Adrian Klaver
    adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>



--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux