Re: how to switch user in postgres

Hi

so 11. 4. 2026 v 19:55 odesílatel ikramuddin <ikram(dot)amani815(at)gmail(dot)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.
>