pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgresql definitive list of network resources used/needed?
12+ messages / 6 participants
[nested] [flat]
* postgresql definitive list of network resources used/needed?
@ 2022-08-10 21:08 richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-10 21:24 ` Re: postgresql definitive list of network resources used/needed? Scott Ribe <scott_ribe@elevated-dev.com>
2022-08-10 21:26 ` Re: postgresql definitive list of network resources used/needed? Ron <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: richard coleman @ 2022-08-10 21:08 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgresql definitive list of network resources used/needed?
2022-08-10 21:08 postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
@ 2022-08-10 21:24 ` Scott Ribe <scott_ribe@elevated-dev.com>
2022-08-10 21:50 ` Re: postgresql definitive list of network resources used/needed? Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 12+ messages in thread
From: Scott Ribe @ 2022-08-10 21:24 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: pgsql-admin
> 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 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.
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 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.
That sounds like you're confused by the fact that when a client connects to a server, the server port is fixed, but the client chooses 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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgresql definitive list of network resources used/needed?
2022-08-10 21:08 postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-10 21:24 ` Re: postgresql definitive list of network resources used/needed? Scott Ribe <scott_ribe@elevated-dev.com>
@ 2022-08-10 21:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-08-10 23:49 ` Re: postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2022-08-10 21:50 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: richard coleman <rcoleman.ascentgl@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgresql definitive list of network resources used/needed?
2022-08-10 21:08 postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-10 21:24 ` Re: postgresql definitive list of network resources used/needed? Scott Ribe <scott_ribe@elevated-dev.com>
2022-08-10 21:50 ` Re: postgresql definitive list of network resources used/needed? Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-08-10 23:49 ` richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-11 00:40 ` Re: postgresql definitive list of network resources used/needed? David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: richard coleman @ 2022-08-10 23:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Ribe <scott_ribe@elevated-dev.com>; pgsql-admin
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
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgresql definitive list of network resources used/needed?
2022-08-10 21:08 postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-10 21:24 ` Re: postgresql definitive list of network resources used/needed? Scott Ribe <scott_ribe@elevated-dev.com>
2022-08-10 21:50 ` Re: postgresql definitive list of network resources used/needed? Tom Lane <tgl@sss.pgh.pa.us>
2022-08-10 23:49 ` Re: postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
@ 2022-08-11 00:40 ` David G. Johnston <david.g.johnston@gmail.com>
2022-08-11 13:17 ` Re: postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: David G. Johnston @ 2022-08-11 00:40 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Scott Ribe <scott_ribe@elevated-dev.com>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgresql definitive list of network resources used/needed?
2022-08-10 21:08 postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-10 21:24 ` Re: postgresql definitive list of network resources used/needed? Scott Ribe <scott_ribe@elevated-dev.com>
2022-08-10 21:50 ` Re: postgresql definitive list of network resources used/needed? Tom Lane <tgl@sss.pgh.pa.us>
2022-08-10 23:49 ` Re: postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-11 00:40 ` Re: postgresql definitive list of network resources used/needed? David G. Johnston <david.g.johnston@gmail.com>
@ 2022-08-11 13:17 ` richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-11 14:13 ` Re: postgresql definitive list of network resources used/needed? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: richard coleman @ 2022-08-11 13:17 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Scott Ribe <scott_ribe@elevated-dev.com>; pgsql-admin
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.
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgresql definitive list of network resources used/needed?
2022-08-10 21:08 postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-10 21:24 ` Re: postgresql definitive list of network resources used/needed? Scott Ribe <scott_ribe@elevated-dev.com>
2022-08-10 21:50 ` Re: postgresql definitive list of network resources used/needed? Tom Lane <tgl@sss.pgh.pa.us>
2022-08-10 23:49 ` Re: postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-11 00:40 ` Re: postgresql definitive list of network resources used/needed? David G. Johnston <david.g.johnston@gmail.com>
2022-08-11 13:17 ` Re: postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
@ 2022-08-11 14:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 14:38 ` Re: postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2022-08-11 14:13 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Scott Ribe <scott_ribe@elevated-dev.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgresql definitive list of network resources used/needed?
2022-08-10 21:08 postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-10 21:24 ` Re: postgresql definitive list of network resources used/needed? Scott Ribe <scott_ribe@elevated-dev.com>
2022-08-10 21:50 ` Re: postgresql definitive list of network resources used/needed? Tom Lane <tgl@sss.pgh.pa.us>
2022-08-10 23:49 ` Re: postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-11 00:40 ` Re: postgresql definitive list of network resources used/needed? David G. Johnston <david.g.johnston@gmail.com>
2022-08-11 13:17 ` Re: postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-11 14:13 ` Re: postgresql definitive list of network resources used/needed? Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-08-11 14:38 ` richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-11 15:04 ` Re: postgresql definitive list of network resources used/needed? Scott Ribe <scott_ribe@elevated-dev.com>
2022-08-11 15:22 ` Re: postgresql definitive list of network resources used/needed? Johannes Truschnigg <johannes@truschnigg.info>
0 siblings, 2 replies; 12+ messages in thread
From: richard coleman @ 2022-08-11 14:38 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Scott Ribe <scott_ribe@elevated-dev.com>; pgsql-admin
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
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgresql definitive list of network resources used/needed?
2022-08-10 21:08 postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-10 21:24 ` Re: postgresql definitive list of network resources used/needed? Scott Ribe <scott_ribe@elevated-dev.com>
2022-08-10 21:50 ` Re: postgresql definitive list of network resources used/needed? Tom Lane <tgl@sss.pgh.pa.us>
2022-08-10 23:49 ` Re: postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-11 00:40 ` Re: postgresql definitive list of network resources used/needed? David G. Johnston <david.g.johnston@gmail.com>
2022-08-11 13:17 ` Re: postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-11 14:13 ` Re: postgresql definitive list of network resources used/needed? Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 14:38 ` Re: postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
@ 2022-08-11 15:04 ` Scott Ribe <scott_ribe@elevated-dev.com>
2022-08-11 15:11 ` Re: postgresql definitive list of network resources used/needed? Ron <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Scott Ribe @ 2022-08-11 15:04 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
> 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 connect to 5432, from whatever random port the OS assigns to that connection.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgresql definitive list of network resources used/needed?
2022-08-10 21:08 postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-10 21:24 ` Re: postgresql definitive list of network resources used/needed? Scott Ribe <scott_ribe@elevated-dev.com>
2022-08-10 21:50 ` Re: postgresql definitive list of network resources used/needed? Tom Lane <tgl@sss.pgh.pa.us>
2022-08-10 23:49 ` Re: postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-11 00:40 ` Re: postgresql definitive list of network resources used/needed? David G. Johnston <david.g.johnston@gmail.com>
2022-08-11 13:17 ` Re: postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-11 14:13 ` Re: postgresql definitive list of network resources used/needed? Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 14:38 ` Re: postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-11 15:04 ` Re: postgresql definitive list of network resources used/needed? Scott Ribe <scott_ribe@elevated-dev.com>
@ 2022-08-11 15:11 ` Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Ron @ 2022-08-11 15:11 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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 connect to 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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgresql definitive list of network resources used/needed?
2022-08-10 21:08 postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-10 21:24 ` Re: postgresql definitive list of network resources used/needed? Scott Ribe <scott_ribe@elevated-dev.com>
2022-08-10 21:50 ` Re: postgresql definitive list of network resources used/needed? Tom Lane <tgl@sss.pgh.pa.us>
2022-08-10 23:49 ` Re: postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-11 00:40 ` Re: postgresql definitive list of network resources used/needed? David G. Johnston <david.g.johnston@gmail.com>
2022-08-11 13:17 ` Re: postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-11 14:13 ` Re: postgresql definitive list of network resources used/needed? Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 14:38 ` Re: postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
@ 2022-08-11 15:22 ` Johannes Truschnigg <johannes@truschnigg.info>
1 sibling, 0 replies; 12+ messages in thread
From: Johannes Truschnigg @ 2022-08-11 15:22 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: pgsql-admin
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
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgresql definitive list of network resources used/needed?
2022-08-10 21:08 postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
@ 2022-08-10 21:26 ` Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Ron @ 2022-08-10 21:26 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2022-08-11 15:22 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-08-10 21:08 postgresql definitive list of network resources used/needed? richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-10 21:24 ` Scott Ribe <scott_ribe@elevated-dev.com>
2022-08-10 21:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-08-10 23:49 ` richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-11 00:40 ` David G. Johnston <david.g.johnston@gmail.com>
2022-08-11 13:17 ` richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-11 14:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 14:38 ` richard coleman <rcoleman.ascentgl@gmail.com>
2022-08-11 15:04 ` Scott Ribe <scott_ribe@elevated-dev.com>
2022-08-11 15:11 ` Ron <ronljohnsonjr@gmail.com>
2022-08-11 15:22 ` Johannes Truschnigg <johannes@truschnigg.info>
2022-08-10 21:26 ` Ron <ronljohnsonjr@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox