Postgres Pro
Downloads
Home   >   mailing lists

Thread: Trigger function always logs postgres as user name

Trigger function always logs postgres as user name

From
Alexander Reichstadt
Date:
Hi,

I setup trigger functions for logging, and while they do work and get triggered, the current_user always insert
“postgres”even when updates/deletes/inserts are caused by users of another name. 

How do I get it to use the name that caused the update? It seems current_user is the trigger’s user, so the server
itselfin some way. This is on PG10 

Here the function:
        BEGIN

                IF      TG_OP = 'INSERT'

                THEN

                        INSERT INTO logging (tabname, schemaname, who, operation, new_val)

                                VALUES (TG_RELNAME, TG_TABLE_SCHEMA, current_user, TG_OP, row_to_json(NEW));

                        RETURN NEW;

                ELSIF   TG_OP = 'UPDATE'

                THEN

                        INSERT INTO logging (tabname, schemaname, who, operation, new_val, old_val)

                                VALUES (TG_RELNAME, TG_TABLE_SCHEMA, current_user, TG_OP,

                                        row_to_json(NEW), row_to_json(OLD));

                        RETURN NEW;

                ELSIF   TG_OP = 'DELETE'

                THEN

                        INSERT INTO logging (tabname, schemaname, operation, who, old_val)

                                VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, current_user, row_to_json(OLD));

                        RETURN OLD;

                END IF;

        END;



Cheers,
Alex

Re: Trigger function always logs postgres as user name

From
Francisco Olarte
Date:
Alexander:

On Sat, Feb 9, 2019 at 1:32 PM Alexander Reichstadt <lxr@me.com> wrote:
> I setup trigger functions for logging, and while they do work and get triggered, the current_user always insert
“postgres”even when updates/deletes/inserts are caused by users of another name. 
> How do I get it to use the name that caused the update? It seems current_user is the trigger’s user, so the server
itselfin some way. This is on PG10 

Maybe your trigger has been defined by postgres and you are using
(from https://www.postgresql.org/docs/11/functions-info.html)
   current_user, name, user name of current execution context
instead of
   session_user, name, session user name

"The session_user is normally the user who initiated the current
database connection; but superusers can change this setting with SET
SESSION AUTHORIZATION. The current_user is the user identifier that is
applicable for permission checking. Normally it is equal to the
session user, but it can be changed with SET ROLE. It also changes
during the execution of functions with the attribute SECURITY DEFINER.
In Unix parlance, the session user is the “real user” and the current
user is the “effective user”. current_role and user are synonyms for
current_user. (The SQL standard draws a distinction between
current_role and current_user, but PostgreSQL does not, since it
unifies users and roles into a single kind of entity.)"

Francisco Olarte.

Re: Trigger function always logs postgres as user name

From
Alexander Reichstadt
Date:
HI,

The answer to the question is that you need to use session_user instead of user or current_user.

Cheers,
Alex


> On 9 Feb 2019, at 10:08, Alexander Reichstadt <lxr@me.com> wrote:
>
> Hi,
>
> I setup trigger functions for logging, and while they do work and get triggered, the current_user always insert
“postgres”even when updates/deletes/inserts are caused by users of another name. 
>
> How do I get it to use the name that caused the update? It seems current_user is the trigger’s user, so the server
itselfin some way. This is on PG10 
>
> Here the function:
>        BEGIN
>
>                IF      TG_OP = 'INSERT'
>
>                THEN
>
>                        INSERT INTO logging (tabname, schemaname, who, operation, new_val)
>
>                                VALUES (TG_RELNAME, TG_TABLE_SCHEMA, current_user, TG_OP, row_to_json(NEW));
>
>                        RETURN NEW;
>
>                ELSIF   TG_OP = 'UPDATE'
>
>                THEN
>
>                        INSERT INTO logging (tabname, schemaname, who, operation, new_val, old_val)
>
>                                VALUES (TG_RELNAME, TG_TABLE_SCHEMA, current_user, TG_OP,
>
>                                        row_to_json(NEW), row_to_json(OLD));
>
>                        RETURN NEW;
>
>                ELSIF   TG_OP = 'DELETE'
>
>                THEN
>
>                        INSERT INTO logging (tabname, schemaname, operation, who, old_val)
>
>                                VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, current_user, row_to_json(OLD));
>
>                        RETURN OLD;
>
>                END IF;
>
>        END;
>
>
>
> Cheers,
> Alex