Re: Inserts restricted to a trigger

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