Thread
-
how to switch user in postgres
ikramuddin <ikram.amani815@gmail.com> — 2026-04-11T17:55:31Z
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. -
Re: how to switch user in postgres
Pavel Stehule <pavel.stehule@gmail.com> — 2026-04-11T18:00:31Z
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. >
-
Re: how to switch user in postgres
David G. Johnston <david.g.johnston@gmail.com> — 2026-04-11T18:33:58Z
This seems quite misplaced on the -performance mailing list. On Sat, Apr 11, 2026 at 10:55 AM ikramuddin <ikram.amani815@gmail.com> wrote: > > 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. > Intentional, every object has its own permissions that are granted to roles independently of others. > finance=> reset role' > finance'> ; > finance'> '; > ERROR: syntax error at or near "' > ; > '" > LINE 1: reset role' > ^ > finance=> reset role; > RESET > You had a typo in the first attempt... For everything else I really don't feel like trying to read your mind. If you have questions about specific outcomes or whatnot please ask them directly. But there are no known bugs here and I presume you haven't demonstrated any - everything is working as intended. David J. -
Re: how to switch user in postgres
Tom Lane <tgl@sss.pgh.pa.us> — 2026-04-11T19:29:06Z
"David G. Johnston" <david.g.johnston@gmail.com> writes: > This seems quite misplaced on the -performance mailing list. Indeed. > On Sat, Apr 11, 2026 at 10:55 AM ikramuddin <ikram.amani815@gmail.com> > wrote: >> plz guide it is a bug or this behavious left intentionally. > Intentional, every object has its own permissions that are granted to roles > independently of others. Also, "REVOKE CONNECT ON DATABASE finance FROM simon" is probably a no-op, because nobody ever did "GRANT CONNECT ON DATABASE finance TO simon". Rather, the reason simon can connect is that there's a default "GRANT CONNECT ... TO public". If you want to restrict CONNECT privileges, you have to revoke that and then hand out the privilege selectively to users that should have it. regards, tom lane
-
Query performance
Dirschel, Steve-CW <steve.dirschel@bestbuy.com> — 2026-04-27T15:52:06Z
Aurora Postgres version 17.4. Table in question: \d poslog_publisher_rms_stage Table "public.poslog_publisher_rms_stage" Column | Type | Collation | Nullable | Default -------------------+--------------------------+-----------+----------+--------- stage_id | uuid | | not null | status | character varying(100) | | | message_body | text | | not null | error_code | character varying(100) | | | error_category | character varying(100) | | | error_message | text | | | error_retry_count | integer | | | 0 create_date | timestamp with time zone | | not null | now() modified_date | timestamp with time zone | | not null | now() Indexes: "poslog_publisher_rms_stage_pkey" PRIMARY KEY, btree (stage_id) "idx_poslog_publisher_stage_create_date_col" btree (create_date) "idx_poslog_publisher_stage_status_error_retry_count_modi_date_c" btree (status, error_retry_count, modified_date) Referenced by: TABLE "poslog_publisher_rms_detail" CONSTRAINT "fk_poslog_publisher_detail_stage_id" FOREIGN KEY (stage_id) REFERENCES poslog_publisher_rms_stage(stage_id) Publications: “sashpsrms_publication" The table is constantly getting loaded into. Rows are inserted with a status ready and then there is a different process looking for that status and will update to processed after processing the row. We have multiple tables like this. Then every 2 hours a different process runs this query looking for failed or unprocessed rows: select ppse.stage_id as stageId, ppse.status as status, ppse.message_body as messageBody from poslog_publisher_rms_stage ppse where ppse.status in ('UNPROCESSED','FAILED') and ppse.error_retry_count < 3 order by ppse.create_date limit 100; If I run that query with explain it is doing a ton of work to find 0 rows. The index it uses seems appropriate for the query. QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=10.92..10.93 rows=1 width=1019) (actual time=66566.823..66566.824 rows=0 loops=1) Buffers: shared hit=1509768 read=2011479 I/O Timings: shared read=79792.017 -> Sort (cost=10.92..10.93 rows=1 width=1019) (actual time=66566.821..66566.821 rows=0 loops=1) Sort Key: create_date Sort Method: quicksort Memory: 25kB Buffers: shared hit=1509768 read=2011479 I/O Timings: shared read=79792.017 -> Index Scan using idx_poslog_publisher_stage_status_error_retry_count_modi_date_c on poslog_publisher_rms_stage ppse (cost=0.57..10.91 rows=1 width=1019) (actual time=66566.761..66566.761 rows=0 loops=1) Index Cond: (((status)::text = ANY ('{UNPROCESSED,FAILED}'::text[])) AND (error_retry_count < 3)) Buffers: shared hit=1509765 read=2011479 I/O Timings: shared read=79792.017 Planning: Buffers: shared hit=195 read=1 I/O Timings: shared read=1.038 Planning Time: 2.909 ms Execution Time: 66581.498 ms The query did 3.5 million block reads when scanning the index of which 1.5 million were in memory and 2 million were from disk. 5 seconds later I ran the exact same query again: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=10.92..10.93 rows=1 width=1019) (actual time=23.589..23.591 rows=0 loops=1) Buffers: shared hit=18736 -> Sort (cost=10.92..10.93 rows=1 width=1019) (actual time=23.588..23.589 rows=0 loops=1) Sort Key: create_date Sort Method: quicksort Memory: 25kB Buffers: shared hit=18736 -> Index Scan using idx_poslog_publisher_stage_status_error_retry_count_modi_date_c on poslog_publisher_rms_stage ppse (cost=0.57..10.91 rows=1 width=1019) (actual time=23.583..23.584 rows=0 loops=1) Index Cond: (((status)::text = ANY ('{UNPROCESSED,FAILED}'::text[])) AND (error_retry_count < 3)) Buffers: shared hit=18736 Planning Time: 0.118 ms Execution Time: 23.628 ms Now it only did 18k block reads all in memory. It used the same index, it also returned 0 rows. Between those 2 runs I looked at pg_stat_user_tables and could see the n_tup_ins increased by 13, n_tup_del increased by 13, n_live_tup increased by 13, and n_dead_tup increased by 13. n_live_tup was 96 million and n_dead_tup was 8.4 million. 18k logical reads to find 0 rows is still high but I believe that is most likely caused by the 8.4 million n_dead_tups. 15 minutes later I ran the query again: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=10.92..10.93 rows=1 width=1019) (actual time=59431.795..59431.796 rows=0 loops=1) Buffers: shared hit=1286676 read=1734000 I/O Timings: shared read=70153.578 -> Sort (cost=10.92..10.93 rows=1 width=1019) (actual time=59431.794..59431.794 rows=0 loops=1) Sort Key: create_date Sort Method: quicksort Memory: 25kB Buffers: shared hit=1286676 read=1734000 I/O Timings: shared read=70153.578 -> Index Scan using idx_poslog_publisher_stage_status_error_retry_count_modi_date_c on poslog_publisher_rms_stage ppse (cost=0.57..10.91 rows=1 width=1019) (actual time=59431.789..59431.790 rows=0 loops=1) Index Cond: (((status)::text = ANY ('{UNPROCESSED,FAILED}'::text[])) AND (error_retry_count < 3)) Buffers: shared hit=1286676 read=1734000 I/O Timings: shared read=70153.578 Planning Time: 0.114 ms Execution Time: 59431.839 ms Total blocks reads increased from 18k to 3 million. n_tup_ins, n_tup_del, n_live_tup, and n_dead_tup all increased by 10,320 over that 15 minute period of time. What is going on here where this query has to do 3+ million block reads to find 0 rows? And how is it possible when I run the query 2 times in a row the logical reads from the 2nd run comes down significantly? Is this somehow related to determining if rows are visible or something like that? When I waited 15 minutes between runs the inserted/updated rows only increased by 10.3k yet total block reads increased by 3 million. Thanks -
Re: Query performance
Laurenz Albe <laurenz.albe@cybertec.at> — 2026-04-28T05:33:24Z
On Mon, 2026-04-27 at 15:52 +0000, Dirschel, Steve-CW wrote: > Aurora Postgres version 17.4. Aurora works quite differently, as far as storage is concerned, so you may be suffering from some peculiarity of that closed source software. > Table in question: > > > \d poslog_publisher_rms_stage > Table "public.poslog_publisher_rms_stage" > Column | Type | Collation | Nullable | Default > -------------------+--------------------------+-----------+----------+--------- > stage_id | uuid | | not null | > status | character varying(100) | | | > message_body | text | | not null | > error_code | character varying(100) | | | > error_category | character varying(100) | | | > error_message | text | | | > error_retry_count | integer | | | 0 > create_date | timestamp with time zone | | not null | now() > modified_date | timestamp with time zone | | not null | now() > Indexes: > "poslog_publisher_rms_stage_pkey" PRIMARY KEY, btree (stage_id) > "idx_poslog_publisher_stage_create_date_col" btree (create_date) > "idx_poslog_publisher_stage_status_error_retry_count_modi_date_c" btree (status, error_retry_count, modified_date) > Referenced by: > TABLE "poslog_publisher_rms_detail" CONSTRAINT "fk_poslog_publisher_detail_stage_id" FOREIGN KEY (stage_id) REFERENCES poslog_publisher_rms_stage(stage_id) > Publications: > “sashpsrms_publication" > > The table is constantly getting loaded into. Rows are inserted with a status ready and > then there is a different process looking for that status and will update to processed > after processing the row. We have multiple tables like this. > > Then every 2 hours a different process runs this query looking for failed or unprocessed rows: > > select > ppse.stage_id as stageId, > ppse.status as status, > ppse.message_body as messageBody > from > poslog_publisher_rms_stage ppse > where > ppse.status in ('UNPROCESSED','FAILED') > and ppse.error_retry_count < 3 > order by > ppse.create_date > limit 100; > > If I run that query with explain it is doing a ton of work to find 0 rows. The index it > uses seems appropriate for the query. The index can be used, but it is far from perfect. The ideal index would be: CREATE INDEX ON poslog_publisher_rms_stage (create_date) WHERE status in ('UNPROCESSED','FAILED') AND error_retry_count < 3; > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > Limit (cost=10.92..10.93 rows=1 width=1019) (actual time=66566.823..66566.824 rows=0 loops=1) > Buffers: shared hit=1509768 read=2011479 > I/O Timings: shared read=79792.017 > -> Sort (cost=10.92..10.93 rows=1 width=1019) (actual time=66566.821..66566.821 rows=0 loops=1) > Sort Key: create_date > Sort Method: quicksort Memory: 25kB > Buffers: shared hit=1509768 read=2011479 > I/O Timings: shared read=79792.017 > -> Index Scan using idx_poslog_publisher_stage_status_error_retry_count_modi_date_c on poslog_publisher_rms_stage ppse (cost=0.57..10.91 rows=1 width=1019) (actual time=66566.761..66566.761 rows=0 loops=1) > Index Cond: (((status)::text = ANY ('{UNPROCESSED,FAILED}'::text[])) AND (error_retry_count < 3)) > Buffers: shared hit=1509765 read=2011479 > I/O Timings: shared read=79792.017 > Planning: > Buffers: shared hit=195 read=1 > I/O Timings: shared read=1.038 > Planning Time: 2.909 ms > Execution Time: 66581.498 ms > > The query did 3.5 million block reads when scanning the index of which 1.5 million were > in memory and 2 million were from disk. 5 seconds later I ran the exact same query again: > > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > Limit (cost=10.92..10.93 rows=1 width=1019) (actual time=23.589..23.591 rows=0 loops=1) > Buffers: shared hit=18736 > -> Sort (cost=10.92..10.93 rows=1 width=1019) (actual time=23.588..23.589 rows=0 loops=1) > Sort Key: create_date > Sort Method: quicksort Memory: 25kB > Buffers: shared hit=18736 > -> Index Scan using idx_poslog_publisher_stage_status_error_retry_count_modi_date_c on poslog_publisher_rms_stage ppse (cost=0.57..10.91 rows=1 width=1019) (actual time=23.583..23.584 rows=0 loops=1) > Index Cond: (((status)::text = ANY ('{UNPROCESSED,FAILED}'::text[])) AND (error_retry_count < 3)) > Buffers: shared hit=18736 > Planning Time: 0.118 ms > Execution Time: 23.628 ms > > Now it only did 18k block reads all in memory. It used the same index, it also > returned 0 rows. Between those 2 runs I looked at pg_stat_user_tables and could see the > n_tup_ins increased by 13, n_tup_del increased by 13, n_live_tup increased by 13, and > n_dead_tup increased by 13. n_live_tup was 96 million and n_dead_tup was 8.4 million. > 18k logical reads to find 0 rows is still high but I believe that is most likely caused > by the 8.4 million n_dead_tups. > > What is going on here where this query has to do 3+ million block reads to find 0 rows? > And how is it possible when I run the query 2 times in a row the logical reads from the > 2nd run comes down significantly? Is this somehow related to determining if rows are > visible or something like that? The only way I can imagine this happening in PostgreSQL is if the first execution kills a lot of index tuples (https://www.cybertec-postgresql.com/en/killed-index-tuples/). > When I waited 15 minutes between runs the inserted/updated rows only increased by > 10.3k yet total block reads increased by 3 million. Now that seems to speak against the above theory, so you are suffering from some Amazon- specific behavior. Yours, Laurenz Albe