Re: postgresql definitive list of network resources used/needed?

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Scott Ribe <scott_ribe(at)elevated-dev(dot)com> writes:
> >> On Aug 10, 2022, at 3:08 PM, richard coleman <
> rcoleman(dot)ascentgl(at)gmail(dot)com> 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
>