Tom & Scott,
Thanks for getting back to me so quickly. Yes the loop back connection is the stats collector. I only mentioned it as an illustration of ports, other than the common tcp connection port (default 5432) that postgres appeared to be using.
I currently manage a number of pg servers ranging from versions 9.x-14.x. Hopefully, I'll be standing up a bunch more (v 14.x) in the near future. If I had provided a specific postgres set up, then the listing of ports used/needed wouldn't be a comprehensive listing, just a listing for that particular setup. I have boxes using physical replication, some using logical, some both. Some have postgre_fdw, some oracle_fdw, some have various other *_fdw, some both or all of the above. Some use postGIS, some don't. Some have a dozen or more active extensions, others only have a handful.
Hence my desire for a listing of the network resources needed by postgres and any of its optional add-ons. I know postgres itself lets you set a main connection port from x-y. Unfortunately, that where whatever documentation I can find ends. There was no mention of the stats collector, nor any replication resources, no mention of what ports each of the various extensions may require. If I wrote a set of firewall rules that blocked all outgoing ports, save a handful, and blocked all incoming ports save 5432 (assuming I am using the default) would postgres still work? If I turned off UDP? What about with all the extensions loaded and in use? Physical replication? Logical replication? Would any of these fail because of a blocked port? If not great, someone should include that in the docs. If so, why? Which protocol, which ports, which features or extensions?
Hopefully you can see why having this very basic documentation would be incredibly helpful and how it's absence is sorely missed.
Thanks again,
rik.
On Wed, Aug 10, 2022 at 5:50 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Scott Ribe <scott_ribe@xxxxxxxxxxxxxxxx> writes:
>> On Aug 10, 2022, at 3:08 PM, richard coleman <rcoleman.ascentgl@xxxxxxxxx> wrote:
>> Apparently, I've discovered that PostgreSQL connects to itself over UDP.
> No it doesn't. (It can use Unix domain sockets for local connections.)
Possibly Richard is seeing the stats collector pipe, which is TCP local
loopback on a random port number. (That'll be gone in v15, but it
exists in all current releases.)
> I've also discovered, while looking at the network while one of our server was running PostgreSQL, that while there were a number of connections between local 5432 and a random remote port, there was also a connection from a local random port to a remote 5432. If these ports were closed and only 5432 left open, this connection would have failed.
postgres_fdw outgoing connection to a remote server? Maybe it's something
else, but that's the most obvious theory.
The incoming-connection port and the stats loopback are the only
IP connections in a bare-bones Postgres system, but I think few
people run it completely bare-bones. There's no such thing as
a "definitive list" of ports used as long as you've told us
nothing about (for example) your backup or replication setup,
not to mention postgres_fdw and other extensions you might use.
regards, tom lane
