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 1wBcYf-001EBF-32 for pgsql-performance@arkaria.postgresql.org; Sat, 11 Apr 2026 17:55:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wBcYe-00HMg8-0R for pgsql-performance@arkaria.postgresql.org; Sat, 11 Apr 2026 17:55:49 +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 1wBcYd-00HMg0-2Z for pgsql-performance@lists.postgresql.org; Sat, 11 Apr 2026 17:55:48 +0000 Received: from mail-oo1-xc2f.google.com ([2607:f8b0:4864:20::c2f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wBcYc-00000000Y6X-2tya for pgsql-performance@lists.postgresql.org; Sat, 11 Apr 2026 17:55:48 +0000 Received: by mail-oo1-xc2f.google.com with SMTP id 006d021491bc7-68a253b7301so1670042eaf.1 for ; Sat, 11 Apr 2026 10:55:46 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1775930144; cv=none; d=google.com; s=arc-20240605; b=YnHZuWjDXFgGC3wxmMmZnCQqiZvdwKL5SzhYm/X6rCpKegfjWb9UMz0ImCAcPhaqiX qBVHE2YUUC639IPs7RfPxz/JCkEZvN+I4zcmF7x8Kd3zjd8IXqgVvNDQVhHh7PPAOLiH d3y5zg9kQ3JJbn3Ub/c3ZuHGBOqDdy4+KZKY1AOcbtcsmsaFN09picnzeX/Ionuap+pc gQqnHSb1YYKU49/kyQ4lzCridctQeKzsaUx2xEhvJ67eu/4WtdoMdXn45jIMGioQ+UQS nguTW/MEXvIeA5Nh7S46I+m/kaG2519SEZ5et3vQISHckjfoFUFL9KkQyiNEFGctfO03 KeDA== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=to:subject:message-id:date:from:mime-version:dkim-signature; bh=heXmTLWo9Cngf5ymKSordQla8Xod3D5zPlAPIB2cmFU=; fh=z/JQ2tSXVGoYKKvtD/yyy3g3xqdpsR82Ngoo7GcsvL4=; b=MYcJnLakyuygJAHq+ifUWSWcbaoluyFnYo1H4AhtAjt8cOi78fQCrp2LvuOjtWBPsv ZgHBhDq2tSgsa52i+hLqftR6Yn/r2K9hlH8ULlsX+QtqFDfYxmrARkI57LPMk5NiL978 GJg+vdcpGKAgpA32URWoRkHge0r0xX2ihptofDA9JiEBHQwly2oG62o9M4CzgUHhjqU2 rSkDUpWzAuISV0dxHg3XMNRUyjoqXASQSstEsL1QKoU+NTWNrx58KQklV4r66YRhLdvR xoBf9vV50Mcgbv6KxT+1DqO3iKS4ZVR+MsGwDBkS0tbgY+G173lZksGk09aC5xIWk0Fp tVTg==; 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=1775930144; x=1776534944; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=heXmTLWo9Cngf5ymKSordQla8Xod3D5zPlAPIB2cmFU=; b=HhGOS+oFGDvckZmb7zpMXLObC4cU2yah//QqEKc3iN9KvzSNkyECVyzFzoaiq9jaAm N7J1s0dQZnrCW9tYeud9of0/WjQRBqLqzCCoVrf89u/Led3Pw/hI2KCarMH8PZBHN+TW 0UWtodwE9K6g/Wmh4XSI6HPjuBOWrscQ4Q0o+zMipyLnblbjXgOxgsb8QITfGXbIUCBf 6jfbqRk2YjfbAY7vyoQSAcUAaEssgU0iBUes5gf6D1h2sPfCDIYkIlCLL7qCucw1oLtv tvoi/rOXXCd76e46mZcfClIbv0p/aAXSOg4GcS/C01f2rv4oNJJwgW8bG8HWE43w5j+C cf7A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1775930144; x=1776534944; h=to:subject:message-id:date:from:mime-version:x-gm-gg :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=heXmTLWo9Cngf5ymKSordQla8Xod3D5zPlAPIB2cmFU=; b=lWR4/kNvUrQnoaNH2Kqg9R1yZEyxgaRQGAVktxr0x4CqtpzbUFQvfNcOHnCt22k/mo yltAnlpbz/3M1+zl7JaVslZzDkpLyssHccMHiRvwHDXKpDVaE1YEb6cajPj2arvQ9AtM HbHX0oefQilgiWYg891iV7KqUjDHxfnCtEhEb6iez56C9aBkxJLKH1F/qCwcrqswJSVP RhbHUR2SRk2dna4n/XrQUHoqZtulZonqW98G3mE9js48j2Z5WQHL57K4DuI3jJBjgyVg dqarjwlHHHQXGVH4ooJkNzcywoZ/ge3/rjNXNTEeGtrRbqxxd4ihmTk5tgWdmTG8NeMz dniQ== X-Gm-Message-State: AOJu0Yxjib5keJXW/JkTUYZLc2I+j0tT+tjKKtZ+T4ev3J4aVwlpKKsF KB0VKNt/1/AhyxUQnz70DaX5KXVkx7T2c53g8hBeM1TKb9VJJeWzCUbXgsNlhp42wiic+GXuIJV +8W4v8rO+sppKpKdU+j9c+c6gQV5vTLuM+ifRn88= X-Gm-Gg: AeBDies0R+LgpOkbujS6tg/wwiaG9Jzb2ITPvgsEmMdtvL7+GwihF9i2BgQ/R9ST36C IAE/jD+eYuAJJkwyAlVSpjFQ5OHiINArf3RN2OCX2Q3dOXIE16p2ruMIsbVivZNQXVSLmWXYaS2 U5ipVyaLm3+UtU3reaCRHF13TKGS54mUiXH9SteeKt4X548o7Fx4TqZ0oKln3R9BP6WiUB1gNzy B/tAp4JcoBRNoIWfaHJ2j5kgpLTy9RdZXbyFnNtVwlU0zGuOrQCKnQcnf2ojaSaLj/D4tpoCHWR KexkTFY= X-Received: by 2002:a05:6820:201b:b0:67e:160c:36c3 with SMTP id 006d021491bc7-68be85d40d5mr4168510eaf.48.1775930144360; Sat, 11 Apr 2026 10:55:44 -0700 (PDT) MIME-Version: 1.0 From: ikramuddin Date: Sat, 11 Apr 2026 23:25:31 +0530 X-Gm-Features: AQROBzAPv8_U5PW6Y4h9OyIuwcalgP7z0ANcbKaIXqu23l74rILtDQC5uf4J4Mc Message-ID: Subject: how to switch user in postgres To: pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000668c17064f32f5a2" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000668c17064f32f5a2 Content-Type: text/plain; charset="UTF-8" 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=> SET ROLE postgres; SET finance=# REVOKE CONNECT ON DATABASE finance FROM simon; REVOKE finance=# set role simon; SET finance=> 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=> SET ROLE postgres; SET finance=# REVOKE CONNECT ON DATABASE finance FROM simon; REVOKE USAGE ON SCHEMA accounting FROM simon; REVOKE SELECT, INSERT, UPDATE, DELETE ON accounting.invoices FROM simon; REVOKE REVOKE REVOKE finance=# 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=# CREATE TABLE accounting.customers( customer_id serial PRIMARY KEY, name TEXT, address TEXT ); CREATE TABLE finance=# SET ROLE simon; SET finance=> select current_user; current_user -------------- simon (1 row) finance=> reset role' finance'> ; finance'> '; ERROR: syntax error at or near "' ; '" LINE 1: reset role' ^ finance=> reset role; RESET finance=# \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 | | | =Tc/postgres + | | | | | | | | postgres=CTc/postgres + | | | | | | | | accounting_ro=c/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 | | | =c/postgres + | | | | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres + | | | | | | | | postgres=CTc/postgres (4 rows) finance=# revoke connect on database finance from simon; REVOKE finance=# \c postgres You are now connected to database "postgres" as user "postgres". postgres=# revoke connect on database finance from simon; REVOKE postgres=# \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=# \c finance You are now connected to database "finance" as user "postgres". finance=# \c postgres You are now connected to database "postgres" as user "postgres". postgres=# \c finance postgres You are now connected to database "finance" as user "postgres". finance=# finance=# set role simon finance-# ; SET finance=> select current_role; current_role -------------- simon (1 row) finance=> SELECT * FROM accounting.invoices; ERROR: permission denied for schema accounting LINE 1: SELECT * FROM accounting.invoices; ^ finance=> ^C thanks and regards Ikramuddin Database lead. --000000000000668c17064f32f5a2 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
hi team,
As i was working through postgresql user and = role privileges=C2=A0i faced an unexpected behavious=C2=A0when we revoke co= nnect on database from a user then why still we need to revoke usage on sch= ema 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_name, it failed that is per= fect which i expected but when i tried to connect through postgres as a sup= eruser it connect still it is fine. but then i write the command set role s= imon it connected even i revoked the connect privileges from the role . plz= guide it is a bug or this behavious=C2=A0left intentionally.
fin= ance=3D> SET ROLE postgres;
SET
finance=3D# REVOKE CONNECT ON DATA= BASE finance FROM simon;
REVOKE
finance=3D# set role simon;
SETfinance=3D> SELECT * FROM accounting.invoices;
=C2=A0invoice_id | in= voice_date | amount
------------+--------------+--------
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 1 | 2024-03-15 =C2=A0 | 250.50
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 2 | 2024-01-20 =C2=A0 | 110.99
=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 3 | 2024-03-29 =C2=A0 | =C2=A0 1000
(3 rows)

finan= ce=3D> SET ROLE postgres;
SET
finance=3D# REVOKE CONNECT ON DATABA= SE finance FROM simon; REVOKE USAGE ON SCHEMA accounting FROM simon; REVOKE= SELECT, INSERT, UPDATE, DELETE ON accounting.invoices FROM simon;
REVOK= E
REVOKE
REVOKE
finance=3D# CREATE ROLE accounting_ro NOLOGIN; GRA= NT CONNECT ON DATABASE finance TO accounting_ro; GRANT USAGE ON SCHEMA acco= unting TO accounting_ro; GRANT SELECT ON ALL TABLES IN SCHEMA accounting TO= accounting_ro;
CREATE ROLE
GRANT
GRANT
GRANT
finance=3D# CR= EATE TABLE accounting.customers( =C2=A0 customer_id serial PRIMARY KEY, =C2= =A0 name TEXT, =C2=A0 address TEXT );
CREATE TABLE
finance=3D# SET RO= LE simon;
SET
finance=3D> select current_user;
=C2=A0current_us= er
--------------
=C2=A0simon
(1 row)

finance=3D> reset = role'
finance'> ;
finance'> ';
ERROR: =C2=A0= syntax error at or near "'
;
'"
LINE 1: reset ro= le'
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ^=
finance=3D> reset role;
RESET
finance=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 databases
=C2=A0 =C2=A0Name = =C2=A0 =C2=A0| =C2=A0Owner =C2=A0 | Encoding | Locale Provider | =C2=A0 Col= late =C2=A0 | =C2=A0 =C2=A0Ctype =C2=A0 =C2=A0| Locale | 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 | pos= tgres=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_r= o=3Dc/postgres
=C2=A0postgres =C2=A0| postgres | UTF8 =C2=A0 =C2=A0 | li= bc =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=A0= template0 | 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/post= gres
=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 | postgr= es=3DCTc/postgres
(4 rows)

finance=3D# revoke connect on database= finance from simon;
REVOKE
finance=3D# \c postgres
You are now co= nnected to database "postgres" as user "postgres".
p= ostgres=3D# revoke connect on database finance from simon;
REVOKE
pos= tgres=3D# \c finance simon;
connection to server on socket "/run/po= stgresql/.s.PGSQL.5432" failed: FATAL: =C2=A0Peer authentication faile= d for user "simon"
Previous connection kept
postgres=3D# \c= finance
You are now connected to database "finance" as user &= quot;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&qu= ot; as user "postgres".
finance=3D#
finance=3D# set role si= mon
finance-# ;
SET
finance=3D> select current_role;
=C2=A0c= urrent_role
--------------
=C2=A0simon
(1 row)

finance=3D&g= t; SELECT * FROM accounting.invoices;
ERROR: =C2=A0permission denied for= schema accounting
LINE 1: SELECT * FROM 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.
--000000000000668c17064f32f5a2--