Re: how to switch user in postgres - Mailing list pgsql-performance

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.