Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wBcdu-001EFM-1c for pgsql-performance@arkaria.postgresql.org; Sat, 11 Apr 2026 18:01:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wBcdr-00HS0N-1y for pgsql-performance@arkaria.postgresql.org; Sat, 11 Apr 2026 18:01:12 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wBcdr-00HS0F-0t for pgsql-performance@lists.postgresql.org; Sat, 11 Apr 2026 18:01:12 +0000 Received: from mail-yx1-xb135.google.com ([2607:f8b0:4864:20::b135]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wBcdq-00000000Y8z-0YOc for pgsql-performance@lists.postgresql.org; Sat, 11 Apr 2026 18:01:11 +0000 Received: by mail-yx1-xb135.google.com with SMTP id 956f58d0204a3-6501c4857b2so2832060d50.3 for ; Sat, 11 Apr 2026 11:01:09 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1775930468; cv=none; d=google.com; s=arc-20240605; b=E0gbYkCP2NgFu/Oin7v6xoJ5aRRqu0jRspIpTQvYwEmbN/r4DesVL/A/Qlln7WAe16 DcNxud5PXQQ9P96eI9My6JggZdaQ1hLt7k1E6/N3ZQvM230DwiUznmbK/vKzZZAfY3NR D4ydnyK+fM/nSAJ2N/wJj8hY29KEj2PV1SMupT1Yps9fin6pvvq8QlpchmUzZsZTfv96 LcwsJJp4zce/tIdGCovCVacsLRrcJMgd/zZOdYKgGQLDDVBuu+90RkNnJzuP438OtKmF 7EI3g9JGyjGky/rSmSzj+NYBa/6pPyr3b4LqFtqkviG3gW2ptdF7M1teEPRlqJHp4LRa hZ7A== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=LmzLM2ZYkn1I4YcInQxxOWnXDGlkQ88bzhcQ5EFt9LQ=; fh=xZR9MjjhJNvQm6hkfxlTf8sxhjNaxOABb9E5jAhW0do=; b=Xh7amk9p986ahkp1x0EdGGmXTJIQO/4KGEWovik4M2gPBgbfHcFYFq+lMWhEf6M+eL cAar0Gb7DkeAjLGSsfajibLFdxfy+ylU4BoJuu25G48yrRkFbrMXjzpdUJ8XbDEK0Gja zDzpV15t+8LhyQoh1/uBlXOutHKi07HHmkYDHIuhpkvecx8YLk5yCXKSTJp5KR/PfZJm YHL/u5SUr46VrgFXMUtm5caOs23k9YnioVJ1EoD24m77UdvZNhPz6nx2F2Qn7um6E2Ye ibCcwTJ3gV/OyZ2k3VhXPTArldGbcR5o8ao11L9hZYBY6KIxKXS5+Vp47LMdOcjf2pAE yETQ==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1775930468; x=1776535268; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=LmzLM2ZYkn1I4YcInQxxOWnXDGlkQ88bzhcQ5EFt9LQ=; b=W9x3f1y1lMyTPM73my0N59j5ufwvCIFR80fvVWmsxAMRQB6b5IX6yhlpkmp5jiTQ74 hRs+qoB2WM+TSA9B2Seam7428fUValGvyXny+t978dOgnJkGh51y1xxY+0KiFPb2QHcC eC+yZiR5oyCOi24cttAKuaoc45GcAn+mKQqMIlejfMKxQmXHnxBX7tI5KUQ7PWxfJJNf Zqzx7i9Zp6898H6DqVi05elP7YE0xxTvEnUc5Iz8QrSaCxjg5g1TiuHBjRDCE95u1sC4 YRSI/YJXOkj9muK+BQQm2W15Hw7U7UMBarZ2XmaVWnoia6U/eS1dhlkzLyxGIy50lQV0 GBsA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1775930468; x=1776535268; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=LmzLM2ZYkn1I4YcInQxxOWnXDGlkQ88bzhcQ5EFt9LQ=; b=W+/uH1giR4Jk0vIUSLFYNgoyAAOa+qiQMdudsFAgRgughGM7bhcidGfuaU1fwHRl16 CMOxjMmZFbqXN/6UVOimVqykomkd5oHQA8Qk7ds0bh2wVxRONZv/VHBcF7f9fle3wa4i KkxL3MAN/yDjPaqj95FM7H4XVRKRHQpPT9q+pzqgzEghm04iQprv6aAgKb4SbrmMe/u9 q7/vC1w7asdKFA5kkPepJVSD+j3Wm6zZlBpoDlmuVwbvp6lo4j4ISNAyQ0OcWWea+0aJ lzToHFrjAsTxP7Snfu2agKCYoPCM48I6wWHmX5FHWkx7X4JEUhchriHx0bevN+/6XMxI QQxA== X-Gm-Message-State: AOJu0Yxa8yN3JBzpK7vt3APoJAyvDPOdpOmngHJklOQ7sDMjsU2mXWAA VNKNzTlcOhrV7QNtIKxe3yUQAbrFRMC9WbT29giHDjtBFzaAzLyig2Ne0kRAWZ4TcY/iU/pNGhe N3v3+NBH3I8bPtlD3RRFBb7ne9/ozI/s= X-Gm-Gg: AeBDieuZgvLCXbbGMkg6ei6HFi/92b0xVFwQsoiUvwrAjy09ZSWeKb9PzHROWdssZwA uQGy06oiBUjnDvx1SunO82ql5TBsjaBmCnMhdaFDnEHJ5Rpbjcb361GkHs1MBqsx4OMTicHv7RF +FRww/5Tug+IrKbTZ05xF6aO/zfAbBNHgPCJoz1EhUjDpG48H9dAoFl0eZ235DthJpaTfQWu6d7 up7II2WSH/y8Bx5b0BGd/x8WVTqRMxAVh/qmWc9qzqpi0XRrPdj6in/czAcS2RHrHw1CJc/fFXz CUzGrER/jHN1x6PupOiaFduvkd/wkcnFOjDE88rT5BBcLiHPDdhggiWRX6MR4IYhmNQYnDVK/0/ egoWj2G06avN0ypb8s9rwRdybkyDWweV7j0YaSMUKzsVesJ0ykbZH2jq/UQ== X-Received: by 2002:a53:ed4d:0:b0:64a:ec39:dd54 with SMTP id 956f58d0204a3-65198b46e9dmr5548441d50.39.1775930468082; Sat, 11 Apr 2026 11:01:08 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Pavel Stehule Date: Sat, 11 Apr 2026 20:00:31 +0200 X-Gm-Features: AQROBzAP0wrrs68Z0gbCnl_rW7XtWuKOTFi1-sVJKWiahIRC_QyXa9q0E3-LgTk Message-ID: Subject: Re: how to switch user in postgres To: ikramuddin Cc: pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000b22b2b064f3308af" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b22b2b064f3308af Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi so 11. 4. 2026 v 19:55 odes=C3=ADlatel ikramuddin napsal: > hi team, > As i was working through postgresql user and role privileges i faced an > unexpected behavious when we revoke connect on database from a user then > why still we need to revoke usage on schema basis and then revoke select = , > insert , update privileges at table table. second when i did all this one > by one and then tried to connect to same database using \c database_name > user_name, it failed that is perfect which i expected but when i tried to > connect through postgres as a superuser it connect still it is fine. but > then i write the command set role simon it connected even i revoked the > connect privileges from the role . plz guide it is a bug or this > behavious left intentionally. > finance=3D> SET ROLE postgres; > SET > finance=3D# REVOKE CONNECT ON DATABASE finance FROM simon; > REVOKE > finance=3D# set role simon; > SET > finance=3D> SELECT * FROM accounting.invoices; > invoice_id | invoice_date | amount > ------------+--------------+-------- > 1 | 2024-03-15 | 250.50 > 2 | 2024-01-20 | 110.99 > 3 | 2024-03-29 | 1000 > (3 rows) > > finance=3D> SET ROLE postgres; > SET > finance=3D# REVOKE CONNECT ON DATABASE finance FROM simon; REVOKE USAGE O= N > SCHEMA accounting FROM simon; REVOKE SELECT, INSERT, UPDATE, DELETE ON > accounting.invoices FROM simon; > REVOKE > REVOKE > REVOKE > finance=3D# CREATE ROLE accounting_ro NOLOGIN; GRANT CONNECT ON DATABASE > finance TO accounting_ro; GRANT USAGE ON SCHEMA accounting TO > accounting_ro; GRANT SELECT ON ALL TABLES IN SCHEMA accounting TO > accounting_ro; > CREATE ROLE > GRANT > GRANT > GRANT > finance=3D# CREATE TABLE accounting.customers( customer_id serial PRIMA= RY > KEY, name TEXT, address TEXT ); > CREATE TABLE > finance=3D# SET ROLE simon; > SET > finance=3D> select current_user; > current_user > -------------- > simon > (1 row) > > finance=3D> reset role' > finance'> ; > finance'> '; > ERROR: syntax error at or near "' > there is no RESET role command you can use `SET ROLE TO DEFAULT` instead Regards Pavel ; > '" > LINE 1: reset role' > ^ > finance=3D> reset role; > RESET > finance=3D# \l > List of databases > Name | Owner | Encoding | Locale Provider | Collate | > Ctype | Locale | ICU Rules | Access privileges > > -----------+----------+----------+-----------------+-------------+-------= ------+--------+-----------+-------------------------- > finance | postgres | UTF8 | libc | en_US.UTF-8 | > en_US.UTF-8 | | | =3DTc/postgres + > | | | | | > | | | postgres=3DCTc/postgres + > | | | | | > | | | accounting_ro=3Dc/postgres > postgres | postgres | UTF8 | libc | en_US.UTF-8 | > en_US.UTF-8 | | | > template0 | postgres | UTF8 | libc | en_US.UTF-8 | > en_US.UTF-8 | | | =3Dc/postgres + > | | | | | > | | | postgres=3DCTc/postgres > template1 | postgres | UTF8 | libc | en_US.UTF-8 | > en_US.UTF-8 | | | =3Dc/postgres + > | | | | | > | | | postgres=3DCTc/postgres > (4 rows) > > finance=3D# revoke connect on database finance from simon; > REVOKE > finance=3D# \c postgres > You are now connected to database "postgres" as user "postgres". > postgres=3D# revoke connect on database finance from simon; > REVOKE > postgres=3D# \c finance simon; > connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: > FATAL: Peer authentication failed for user "simon" > Previous connection kept > postgres=3D# \c finance > You are now connected to database "finance" as user "postgres". > finance=3D# \c postgres > You are now connected to database "postgres" as user "postgres". > postgres=3D# \c finance postgres > You are now connected to database "finance" as user "postgres". > finance=3D# > finance=3D# set role simon > finance-# ; > SET > finance=3D> select current_role; > current_role > -------------- > simon > (1 row) > > finance=3D> SELECT * FROM accounting.invoices; > ERROR: permission denied for schema accounting > LINE 1: SELECT * FROM accounting.invoices; > ^ > finance=3D> ^C > > thanks and regards > Ikramuddin Database lead. > --000000000000b22b2b064f3308af Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

so 11. 4. 2026 v=C2=A019:55 o= des=C3=ADlatel ikramuddin <i= kram.amani815@gmail.com> napsal:
hi team,
As i was working thro= ugh postgresql user and role privileges=C2=A0i faced an unexpected behaviou= s=C2=A0when we revoke connect on database from a user then why still we nee= d to revoke usage on schema basis and then revoke select , insert , update= =C2=A0privileges at table table. second when i did all this one by one and = then tried to connect to same database using \c database_name=C2=A0 user_na= me, it failed that is perfect which i expected but when i tried to connect = through postgres as a superuser it connect still it is fine. but then i wri= te the command set role simon it connected even i revoked the connect privi= leges from the role . plz guide it is a bug or this behavious=C2=A0left int= entionally.
finance=3D> SET ROLE postgres;
SET
finance= =3D# REVOKE CONNECT ON DATABASE finance FROM simon;
REVOKE
finance=3D= # set role simon;
SET
finance=3D> SELECT * FROM accounting.invoice= s;
=C2=A0invoice_id | invoice_date | amount
------------+------------= --+--------
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 1 | 2024-03-15 =C2=A0 | 2= 50.50
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 2 | 2024-01-20 =C2=A0 | 110.99<= br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 3 | 2024-03-29 =C2=A0 | =C2=A0 1000(3 rows)

finance=3D> SET ROLE postgres;
SET
finance=3D# R= EVOKE CONNECT ON DATABASE finance FROM simon; REVOKE USAGE ON SCHEMA accoun= ting FROM simon; REVOKE SELECT, INSERT, UPDATE, DELETE ON accounting.invoic= es FROM simon;
REVOKE
REVOKE
REVOKE
finance=3D# CREATE ROLE acc= ounting_ro NOLOGIN; GRANT CONNECT ON DATABASE finance TO accounting_ro; GRA= NT USAGE ON SCHEMA accounting TO accounting_ro; GRANT SELECT ON ALL TABLES = IN SCHEMA accounting TO accounting_ro;
CREATE ROLE
GRANT
GRANT
= GRANT
finance=3D# CREATE TABLE accounting.customers( =C2=A0 customer_id = serial PRIMARY KEY, =C2=A0 name TEXT, =C2=A0 address TEXT );
CREATE TABL= E
finance=3D# SET ROLE simon;
SET
finance=3D> select current_us= er;
=C2=A0current_user
--------------
=C2=A0simon
(1 row)
finance=3D> reset role'
finance'> ;
finance'> = ';
ERROR: =C2=A0syntax error at or near "'
<= /blockquote>

there is no RESET role command
you can use `SET ROLE TO DEFAULT` instead

=
Regards

Pavel=C2=A0

;
= 9;"
LINE 1: reset role'
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 ^
finance=3D> reset role;
RESET
fin= ance=3D# \l
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0List of datab= ases
=C2=A0 =C2=A0Name =C2=A0 =C2=A0| =C2=A0Owner =C2=A0 | Encoding | Lo= cale Provider | =C2=A0 Collate =C2=A0 | =C2=A0 =C2=A0Ctype =C2=A0 =C2=A0| L= ocale | ICU Rules | =C2=A0 =C2=A0Access privileges
-----------+---------= -+----------+-----------------+-------------+-------------+--------+-------= ----+--------------------------
=C2=A0finance =C2=A0 | postgres | UTF8 = =C2=A0 =C2=A0 | libc =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| en_US.UTF-8= | en_US.UTF-8 | =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | =3DTc/postgres =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0+
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | postgres=3DCTc/postgres =C2=A0 +
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 | accounting_ro=3Dc/postgres
=C2=A0postgres =C2=A0| po= stgres | UTF8 =C2=A0 =C2=A0 | libc =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0| en_US.UTF-8 | en_US.UTF-8 | =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 |
=C2=A0template0 | postgres | UTF8 =C2=A0 =C2=A0 = | libc =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| en_US.UTF-8 | en_US.UTF-8= | =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =3Dc/p= ostgres =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 +
=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 | postgres=3DCTc/postgres
=C2=A0template1 | postgres | UTF8 = =C2=A0 =C2=A0 | libc =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| en_US.UTF-8= | en_US.UTF-8 | =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | =3Dc/postgres =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 +
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | postgres=3DCTc/postgres
(4 rows)

finan= ce=3D# revoke connect on database finance from simon;
REVOKE
finance= =3D# \c postgres
You are now connected to database "postgres" = as user "postgres".
postgres=3D# revoke connect on database fi= nance from simon;
REVOKE
postgres=3D# \c finance simon;
connection= to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATA= L: =C2=A0Peer authentication failed for user "simon"
Previous = connection kept
postgres=3D# \c finance
You are now connected to data= base "finance" as user "postgres".
finance=3D# \c po= stgres
You are now connected to database "postgres" as user &q= uot;postgres".
postgres=3D# \c finance postgres
You are now conn= ected to database "finance" as user "postgres".
fina= nce=3D#
finance=3D# set role simon
finance-# ;
SET
finance=3D&g= t; select current_role;
=C2=A0current_role
--------------
=C2=A0si= mon
(1 row)

finance=3D> SELECT * FROM accounting.invoices;
= ERROR: =C2=A0permission denied for schema accounting
LINE 1: SELECT * FR= OM accounting.invoices;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 ^
finance=3D> ^C

thanks and regards
Ikramuddin Database lead.
--000000000000b22b2b064f3308af--