Have you tried session_user?
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
cur | sess
----------+-------
postgres | write
On Tue, Jun 18, 2019 at 6:30 PM Miles Elam <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> 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
