Re: Inserts restricted to a trigger - Mailing list pgsql-general

From Miles Elam
Subject Re: Inserts restricted to a trigger
Date
Msg-id CAALojA_ODo7GP_zq6y5s2WBwjbCwMT9AMR0omSQngZur9Bzkxg@mail.gmail.com
Whole thread Raw
In response to Re: Inserts restricted to a trigger  (Torsten Förtsch <tfoertsch123@gmail.com>)
Responses Re: Inserts restricted to a trigger
List pgsql-general
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.

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> 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> 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> 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


pgsql-general by date:

Previous
From: Andres Freund
Date:
Subject: Re: perf tuning for 28 cores and 252GB RAM
Next
From: Tom Lane
Date:
Subject: Re: Is array_append O(n)?