public inbox for pgsql-performance@postgresql.org
help / color / mirror / Atom feedFrom: ikramuddin <ikram.amani815@gmail.com>
To: pgsql-performance@lists.postgresql.org
Subject: how to switch user in postgres
Date: Sat, 11 Apr 2026 23:25:31 +0530
Message-ID: <CAL9MbytWDzHYHPo31AeR=-ZcCKfQr8uXQitFNe0nehAvLze7PA@mail.gmail.com> (raw)
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.
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ikram.amani815@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: how to switch user in postgres
In-Reply-To: <CAL9MbytWDzHYHPo31AeR=-ZcCKfQr8uXQitFNe0nehAvLze7PA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox