Thread: postgresql definitive list of network resources used/needed?
I'm currently standing up a number of PostgreSQL 14 servers. There is a question concerning ports and protocols required or used by PostgreSQL. Unfortunately, I can't seem to find any such listing. Most references only mention the primary connection port, ex: tcp 5432.
Apparently, I've discovered that PostgreSQL connects to itself over UDP. 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.
Does anyone know where I might find the definitive listing of ports and protocols used by PostgreSQL?
Thanks,
rik.
> On Aug 10, 2022, at 3:08 PM, richard coleman <rcoleman.ascentgl@gmail.com> wrote: > > I'm currently standing up a number of PostgreSQL 14 servers. There is a question concerning ports and protocols requiredor used by PostgreSQL. Unfortunately, I can't seem to find any such listing. Most references only mention the primaryconnection port, ex: tcp 5432. There is whatever port you configure it for; the default is 5432. > Apparently, I've discovered that PostgreSQL connects to itself over UDP. No it doesn't. (It can use Unix domain sockets for local connections.) > I've also discovered, while looking at the network while one of our server was running PostgreSQL, that while there werea number of connections between local 5432 and a random remote port, there was also a connection from a local randomport to a remote 5432. If these ports were closed and only 5432 left open, this connection would have failed. That sounds like you're confused by the fact that when a client connects to a server, the server port is fixed, but the clientchooses a random outbound port. > Does anyone know where I might find the definitive listing of ports and protocols used by PostgreSQL? TCP, whatever port you configure it listen on.
On 8/10/22 16:08, richard coleman wrote: > I'm currently standing up a number of PostgreSQL 14 servers. There is a > question concerning ports and protocols required or used by PostgreSQL. > Unfortunately, I can't seem to find any such listing. Most references > only mention the primary connection port, ex: tcp 5432. > > Apparently, I've discovered that PostgreSQL connects to itself over UDP. > 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, That's how TCP IP works; you see the same with ssh, http, dns, etc, etc. > there was also a connection from a local random port to a remote 5432. A local process connecting to a remote postgresql database. > If these ports were closed and only 5432 left open, this connection > would have failed. > > Does anyone know where I might find the definitive listing of ports and > protocols used by PostgreSQL? I'm pretty sure I only ever requuest that TCP 5432 be opened in the firewall, and that as incoming to the DB server. -- Angular momentum makes the world go 'round.
Scott Ribe <scott_ribe@elevated-dev.com> writes:
>> On Aug 10, 2022, at 3:08 PM, richard coleman <rcoleman.ascentgl@gmail.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
werea number of connections between local 5432 and a random remote port, there was also a connection from a local
randomport 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
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@sss.pgh.pa.us> wrote:
Scott Ribe <scott_ribe@elevated-dev.com> writes:
>> On Aug 10, 2022, at 3:08 PM, richard coleman <rcoleman.ascentgl@gmail.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
On Wed, Aug 10, 2022 at 4:49 PM richard coleman <rcoleman.ascentgl@gmail.com> wrote:
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.
[...]
If not great, someone should include that in the docs. If so, why? Which protocol, which ports, which features or extensions?
You qualify as a "someone", care about the issue, and the project is open source.
It is not the place of PostgreSQL's documentation to list requirements for third-party software; they need to do that themselves. Calling them "optional add-ons" implies a level of integration that doesn't apply.
We document that the server listens on TCP, on the configured port number, which has a default value of 5432. That's it. How the client-side TCP/IP stack handles port assignment for outbound connections seems like it is out of scope; is it not our responsibility to document the nuances of TCP/IP. IIUC trying to block outbound connections at the port level doesn't make sense...either block outbound or don't. It seems possible to circumvent any such rule that may exist.
If UDP on the loopback device is in scope here there is a gap in my understanding of sane firewall configurations. I don't see much point in documenting the inner workings that are now obsolete and that should someone decide to block should produce sufficient diagnostic messages in the logs so as to be readily solvable.
David J.
David,
Thank you for your input.
Is it correct to state that (barring the soon to be obsolete stats collector):
- PostgreSQL only uses the configured port, who's default is 5432, via tcp
- outgoing connections are all tcp and use random ports
- none of the other intrinsic features; replication, pg_dump, pg_basebackup, pg_restore, etc. use any other network ports
- network ports used by extensions, even if included in the base install, are beyond the scope of PostgreSQL documentation
Thanks again,
rik.
On Wed, Aug 10, 2022 at 8:40 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, Aug 10, 2022 at 4:49 PM richard coleman <rcoleman.ascentgl@gmail.com> wrote: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.[...]If not great, someone should include that in the docs. If so, why? Which protocol, which ports, which features or extensions?You qualify as a "someone", care about the issue, and the project is open source.It is not the place of PostgreSQL's documentation to list requirements for third-party software; they need to do that themselves. Calling them "optional add-ons" implies a level of integration that doesn't apply.We document that the server listens on TCP, on the configured port number, which has a default value of 5432. That's it. How the client-side TCP/IP stack handles port assignment for outbound connections seems like it is out of scope; is it not our responsibility to document the nuances of TCP/IP. IIUC trying to block outbound connections at the port level doesn't make sense...either block outbound or don't. It seems possible to circumvent any such rule that may exist.If UDP on the loopback device is in scope here there is a gap in my understanding of sane firewall configurations. I don't see much point in documenting the inner workings that are now obsolete and that should someone decide to block should produce sufficient diagnostic messages in the logs so as to be readily solvable.David J.
richard coleman <rcoleman.ascentgl@gmail.com> writes:
> Is it correct to state that (barring the soon to be obsolete stats
> collector):
> - PostgreSQL only uses the configured port, who's default is 5432, via
> tcp
> - outgoing connections are all tcp and use random ports
There are no outgoing connections in basic PG, unless maybe you
enable log_hostname, and then it'd depend on what your libc does
for DNS lookup --- but that'd more likely be UDP than TCP.
> - none of the other intrinsic features; replication, pg_dump,
> pg_basebackup, pg_restore, etc. use any other network ports
Replication surely requires some sort of connection, although it
could be either outgoing or incoming depending on your setup.
I'm also getting rather confused by what you mean by "use a port",
if you think that clients such as pg_dump don't.
> - network ports used by extensions, even if included in the base
> install, are beyond the scope of PostgreSQL documentation
If we were to start adding this sort of info to the docs,
it'd be appropriate to document the behavior of each included
extension in the documentation file for that extension.
As I go through these examples, though, I grow less enthusiastic
about it being a useful exercise at all.
regards, tom lane
Tom,
Thanks again. If you read the entire line you quoted, I was trying to confirm that intrinsic features and utilities, including pg_dump, don't use any **other** network ports, not that they didn't use any at all.
It seems less likely with every exchange that a listing of network ports used/needed does exist. That's a shame.
I guess if I find myself in the situation where I really need that listing, I'll have to dust off my *extremely* rusty C skills and pour through the source code.
Thanks to everyone for taking the time to chime in.
Until next time,
rik.
On Thu, Aug 11, 2022 at 10:13 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
richard coleman <rcoleman.ascentgl@gmail.com> writes:
> Is it correct to state that (barring the soon to be obsolete stats
> collector):
> - PostgreSQL only uses the configured port, who's default is 5432, via
> tcp
> - outgoing connections are all tcp and use random ports
There are no outgoing connections in basic PG, unless maybe you
enable log_hostname, and then it'd depend on what your libc does
for DNS lookup --- but that'd more likely be UDP than TCP.
> - none of the other intrinsic features; replication, pg_dump,
> pg_basebackup, pg_restore, etc. use any other network ports
Replication surely requires some sort of connection, although it
could be either outgoing or incoming depending on your setup.
I'm also getting rather confused by what you mean by "use a port",
if you think that clients such as pg_dump don't.
> - network ports used by extensions, even if included in the base
> install, are beyond the scope of PostgreSQL documentation
If we were to start adding this sort of info to the docs,
it'd be appropriate to document the behavior of each included
extension in the documentation file for that extension.
As I go through these examples, though, I grow less enthusiastic
about it being a useful exercise at all.
regards, tom lane
> On Aug 11, 2022, at 8:38 AM, richard coleman <rcoleman.ascentgl@gmail.com> wrote: > > If you read the entire line you quoted, I was trying to confirm that intrinsic features and utilities, including pg_dump,don't use any **other** network ports Right. "Standard" utilities don't. pg_dump, pg_basebackup, fdw, etc all connect to PG as clients--which means they connectto 5432, from whatever random port the OS assigns to that connection.
On 8/11/22 10:04, Scott Ribe wrote: >> On Aug 11, 2022, at 8:38 AM, richard coleman <rcoleman.ascentgl@gmail.com> wrote: >> >> If you read the entire line you quoted, I was trying to confirm that intrinsic features and utilities, including pg_dump,don't use any **other** network ports > Right. "Standard" utilities don't. pg_dump, pg_basebackup, fdw, etc all connect to PG as clients--which means they connectto 5432, from whatever random port the OS assigns to that connection. Exactly. Postgresql uses 5432. Other RDBMSs (for example SQL Server) use more than just the client port number because they bundle other features into their products and decide to send features like mirroring over different port numbers. -- Angular momentum makes the world go 'round.
On Thu, Aug 11, 2022 at 10:38:51AM -0400, richard coleman wrote: > Tom, > > [...] > > It seems less likely with every exchange that a listing of network ports > used/needed does exist. That's a shame. > > I guess if I find myself in the situation where I really need that listing, > I'll have to dust off my *extremely* rusty C skills and pour through the > source code. You are probably better off using the tools your platform/OS provides you with to gather that information at runtime. Assuming GNU/Linux, there's ss(8) and lsof(8) to superficially monitor TCP connections and activity. If you are lucky enough to have bpftrace(8) available, you can cobble something together on your own, or resort to the provided tcpaccept(8) script to gather interesting data points, and draw your conclusions from that. -- with best regards: - Johannes Truschnigg ( johannes@truschnigg.info ) www: https://johannes.truschnigg.info/ phone: +436502133337 xmpp: johannes@truschnigg.info