public inbox for pgsql-performance@postgresql.org
help / color / mirror / Atom feedFrom: Pavel Stehule <pavel.stehule@gmail.com>
To: ikramuddin <ikram.amani815@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: how to switch user in postgres
Date: Sat, 11 Apr 2026 20:00:31 +0200
Message-ID: <CAFj8pRD7QYEk+QWv2c=A4s7Xr=+oPtaFSP1tc8ENe7=1ae4FDg@mail.gmail.com> (raw)
In-Reply-To: <CAL9MbytWDzHYHPo31AeR=-ZcCKfQr8uXQitFNe0nehAvLze7PA@mail.gmail.com>
References: <CAL9MbytWDzHYHPo31AeR=-ZcCKfQr8uXQitFNe0nehAvLze7PA@mail.gmail.com>
Hi
so 11. 4. 2026 v 19:55 odesÃlatel ikramuddin <ikram.amani815@gmail.com>
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=> 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 "'
>
there is no RESET role command
you can use `SET ROLE TO DEFAULT` instead
Regards
Pavel
;
> '"
> 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: pavel.stehule@gmail.com, ikram.amani815@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: how to switch user in postgres
In-Reply-To: <CAFj8pRD7QYEk+QWv2c=A4s7Xr=+oPtaFSP1tc8ENe7=1ae4FDg@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