To: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
From: "Andrey V. Lepikhov" <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
Subject: SET SESSION AUTHORIZATION command doesn't update status of backend
Message-ID: <76ae5aaf-1843-4d84-0430-f35da7f786b9@postgrespro.ru>
Date: Fri, 15 Oct 2021 11:19:20 +0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.13.0
MIME-Version: 1.0
Content-Type: multipart/mixed;
 boundary="------------A419948B55B5A647B63BA3F0"
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/76ae5aaf-1843-4d84-0430-f35da7f786b9%40postgrespro.ru>
Precedence: bulk

This is a multi-part message in MIME format.
--------------A419948B55B5A647B63BA3F0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

Nikita Degtyarev (n.degtyarev@postgrespro.ru) reported a suspicious 
behaviour of a SET SESSION AUTHORIZATION command. If you switch user in 
a session:

CREATE USER abc;
SET SESSION AUTHORIZATION abc;

command "SELECT SESSION_USER, CURRENT_USER" shows us:

  session_user | current_user
--------------+--------------
  abc          | abc

But if you launch a job:
SELECT pg_sleep(60);

and will see into the pg_stat_activity:

SELECT usename,query FROM pg_stat_activity
WHERE backend_type = 'client backend';

it tell you that this job is executing with the original user:

  usename |                   query
---------+--------------------------------------------
  andrey  | SELECT usename,query FROM pg_stat_activity+
          | WHERE backend_type = 'client backend';
  abc     | SELECT pg_sleep(60);

The deal is in missed update of PgBackendStatus after updating of 
session user.
The attached patch fixes this bug.

-- 
regards,
Andrey Lepikhov
Postgres Professional

--------------A419948B55B5A647B63BA3F0
Content-Type: text/x-patch; charset=UTF-8;
 name="0001-Bugfix.-Update-a-backend-status-userid-field-after-c.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename*0="0001-Bugfix.-Update-a-backend-status-userid-field-after-c.pa";
 filename*1="tch"