BUG #16955: Replication port problems - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      16955
Logged by:          Matt Welch
Email address:      mattwelchwork@gmail.com
PostgreSQL version: 12.5
Operating system:   centos 08
Description:

While creating a async replication arrangement, I hit an unexpected issue
regarding ports

Primary server : 192.168.9.9, postgres running on port 5123
Secondary server : 192.168.9.20 postgres running on port 5121

created a replication user on primary : createuser --replication --pwprompt
--port=5121 repuser01
set up pg_hba.conf : host replication repuser01 192.168.9.1/24 md5


On the secondary, created the cluster folders and files using pg_basebackup
pointing to primary :
pg_basebackup --host 192.168.9.9 --port=5123 --pgdata=/var/lib/pgsql/data01
--username=repuser01 --progress --verbose --write-recovery-conf
--wal-method=stream --create-slot --slot=centos08_010_03

Resulting postgres.auto.conf file - note change of listening port for
standby cluster to 5121:
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
listen_addresses = '*'
port = 5121
max_worker_processes = 2
max_parallel_workers = 2
autovacuum_max_workers = 2
archive_mode = 'on'
archive_command = '/usr/local/bin/pg_ssnc_archive.sh 03 %f %p'
primary_conninfo = 'user=repuser01 password=q1w2e3r4 channel_binding=prefer
host=192.168.9.9 port=5123 sslmode=prefer sslcompression=0
ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres
target_session_attrs=any'
primary_slot_name = 'centos08_010_03'

systemctl start postgresql@general01.service

[root@centos08-010 ~]# systemctl status postgresql@general01.service
● postgresql@general01.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql@.service; enabled;
vendor preset: disabled)
  Drop-In: /etc/systemd/system/postgresql@general01.service.d
           └─30-postgresql-setup.conf
   Active: active (running) since Thu 2021-04-08 14:24:00 BST; 16s ago
  Process: 12927 ExecStartPre=/usr/libexec/postgresql-check-db-dir
postgresql@general01 (code=exited, status=0/SUCCESS)
 Main PID: 12930 (postmaster)
    Tasks: 6 (limit: 49340)
   Memory: 4.4M
   CGroup:
/system.slice/system-postgresql.slice/postgresql@general01.service
           ├─12930 /usr/bin/postmaster -D /var/lib/pgsql/data01
           ├─12931 postgres: logger
           ├─12932 postgres: startup   waiting for
0000000200000000000000C1
           ├─12933 postgres: checkpointer
           ├─12934 postgres: background writer
           └─12935 postgres: stats collector

Apr 08 14:23:58 centos08-010 systemd[1]: Starting PostgreSQL database
server...
Apr 08 14:23:59 centos08-010 postmaster[12930]: 2021-04-08 14:23:59.407 BST
[12930] LOG:  starting PostgreSQL 12.5 on x86_64-redhat-linux-gnu, compiled
by gcc (GCC) 8.4.1 20>
Apr 08 14:23:59 centos08-010 postmaster[12930]: 2021-04-08 14:23:59.408 BST
[12930] LOG:  listening on IPv4 address "0.0.0.0", port 5121
Apr 08 14:23:59 centos08-010 postmaster[12930]: 2021-04-08 14:23:59.408 BST
[12930] LOG:  listening on IPv6 address "::", port 5121
Apr 08 14:23:59 centos08-010 postmaster[12930]: 2021-04-08 14:23:59.455 BST
[12930] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5121"
Apr 08 14:23:59 centos08-010 postmaster[12930]: 2021-04-08 14:23:59.558 BST
[12930] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5121"
Apr 08 14:23:59 centos08-010 postmaster[12930]: 2021-04-08 14:23:59.643 BST
[12930] LOG:  redirecting log output to logging collector process
Apr 08 14:23:59 centos08-010 postmaster[12930]: 2021-04-08 14:23:59.643 BST
[12930] HINT:  Future log output will appear in directory "log".
Apr 08 14:24:00 centos08-010 systemd[1]: Started PostgreSQL database
server.


So the slave is now listening on 5121 with the primary on 5123, read only
connections to the slave work OK, as do read-write to the primart

However, the log on the secondary reports :
2021-04-08 14:29:20.525 BST [13316] FATAL:  could not connect to the primary
server: could not connect to server: Permission denied
                Is the server running on host "192.168.9.9" and accepting
                TCP/IP connections on port 5123?
2021-04-08 14:29:25.534 BST [13320] FATAL:  could not connect to the primary
server: could not connect to server: Permission denied
                Is the server running on host "192.168.9.9" and accepting
                TCP/IP connections on port 5123?
2021-04-08 14:29:30.535 BST [13324] FATAL:  could not connect to the primary
server: could not connect to server: Permission denied
                Is the server running on host "192.168.9.9" and accepting
                TCP/IP connections on port 5123?

this seems odd as psql connections from the secondary to the primary on port
5123 work OK

Selinux messages appear in the /var/log/messages file on the secondary
indicating issues with port 5123 

Apr  8 14:32:43 centos08-010 setroubleshoot[12939]: SELinux is preventing
/usr/bin/postgres from name_connect access on the tcp_socket port
5123.#012#012*****  Plugin connect_ports (92.2 confidence) suggests
*********************#012#012If you want to allow /usr/bin/postgres to
connect to network port 5123#012Then you need to modify the port
type.#012Do#012# semanage port -a -t PORT_TYPE -p tcp 5123#012    where
PORT_TYPE is one of the following: auth_port_t, dns_port_t, dnssec_port_t,
kerberos_port_t, ldap_port_t, ocsp_port_t, postgresql_port_t.#012#012*****
Plugin catchall_boolean (7.83 confidence) suggests
******************#012#012If you want to allow nis to enabled#012Then you
must tell SELinux about this by enabling the 'nis_enabled'
boolean.#012#012Do#012setsebool -P nis_enabled 1#012#012*****  Plugin
catchall (1.41 confidence) suggests   **************************#012#012If
you believe that postgres should be allowed name_connect access on the port
5123 tcp_socket by default.#012Then you should report this as a bug.#012You
can generate a local policy module to allow this access.#012Do#012allow this
access for now by executing:#012# ausearch -c 'postmaster' --raw |
audit2allow -M my-postmaster#012# semodule -X 300 -i my-postmaster.pp#012

Given that port 5123 is operative on the primary rather than the secondary,
why should SELinux on the secondary be recording issues for port 5123 ?

Issue is resolved by running "semanage port -a -t postgresql_port_t -p tcp
5123" on the secondary

Why would the secondary SELinux config need 5123 configured when this is a
primary side port ?