Trigger function always logs postgres as user name

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Trigger function always logs postgres as user name

Alexander Reichstadt-3
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 itself in 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
Reply | Threaded
Open this post in threaded view
|

Re: Trigger function always logs postgres as user name

Francisco Olarte
Alexander:

On Sat, Feb 9, 2019 at 1:32 PM Alexander Reichstadt <[hidden email]> 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 itself in 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.

Reply | Threaded
Open this post in threaded view
|

Re: Trigger function always logs postgres as user name

Alexander Reichstadt
In reply to this post by Alexander Reichstadt-3
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 <[hidden email]> 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 itself in 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