Hi so 11. 4. 2026 v 19:55 odesÃlatel ikramuddin <[email protected]> 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. >
