Postgres connection limits and what should rolconnlimit be set for default roles such as Postgres

user1548815

10/25/22, 2:16 PM

I am locking down a Postgres 12 instance and can't find clear explanation of connection limits and rolconnlimit settings. First I have max_connection set to 100 but noticed that superuser_reserved_connections is set to 3. Does that mean that I have 97 connections effectively? That is not really an issue but I was curious.
The real stumper is how I set the role connection limits? Right now they are all -1(unlimited) but my guidance is that they must be set. Should I set a limit on connections for "postgres" and the other default roles such as pg_monitor, pg_read_all_stats, etc? If I do set them should they be set to something lower that max_connections? This database only has one user, which is an application account so I am assuming setting that to 100 will not be an issue?

133

1 + 0

postgresql

Score:1

Server

Phill W.

10/25/22, 3:41 PM

... max_connection set to 100 ... superuser_reserved_connections is set to 3. Does that mean that I have 97 connections effectively?

Short answer: Yes.

... role connection limits? Right now they are all -1 (unlimited) but my guidance is that they must be set.

"Guidance" from whom, exactly?
If someone is telling you that you must do something, then you would hope that they have at least some idea of how to do so and should be able to tell you this.

Of course, the first thing to do is to lock down your Test Postgres 12 instance and see what breaks. Making changes like this directly in Production would be madness.

0 + 0

user1548815

1/12/23, 2:35 AM

Sorry a little late getting back; but your answer helped. As far as the Guidance comment; not everybody telling you to do something necessarily knows anything about it; they just know there is a checklist to follow and it should be followed. So in that case I need to make sure these settings don't break the system and explain when a setting or configuration should be not implemented. That is why I depend on the expertise on this board and appreciative help such as yours. And your last point can't be understated; I like my job so I test test test.

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: Postgres connection limits and what should rolconnlimit be set for default roles such as Postgres

TH: ขีดจำกัดการเชื่อมต่อของ Postgres และสิ่งที่ควรตั้งค่า rolconnlimit สำหรับบทบาทเริ่มต้น เช่น Postgres

RO: Limitele de conexiune Postgres și ce ar trebui setat rolconnlimit pentru rolurile implicite, cum ar fi Postgres

RU: Ограничения соединений Postgres и то, что следует установить rolconnlimit для ролей по умолчанию, таких как Postgres

VI: Giới hạn kết nối Postgres và rolconnlimit nên được đặt như thế nào cho các vai trò mặc định, chẳng hạn như Postgres

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.