In Ubuntu 18.04.02 Server Edition with postgreSQL-11 I want to add the possibility of SSL communication between the postgreSQL server and a client.
I turned the ssl on in postgresql.conf :
sudo nano /etc/postgresql/11/main/postgresql.conf
ssl = on
and in pg_hba.conf I added a line for ssl :
sudo nano /etc/postgresql/11/main/pg_hba.conf
local all postgres peer
# TYPE DATABASE USER ADDRESS
METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5
host all all 192.168.1.1/24 md5
# Only for SSL connections :
hostssl all all cert
But when reloading the new configuration, and restarting postgreSQL :
(base) marco@pc:~$ sudo /etc/init.d/postgresql reload
[ ok ] Reloading postgresql configuration (via systemctl):
postgresql.service.
(base) marco@pc:~$ sudo service postgresql restart
(base) marco@pc:~$ sudo service postgresql status
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled;
vendor preset: enabled)
Active: active (exited) since Mon 2019-09-16 18:48:13 CEST; 6s
ago
Process: 3349 ExecReload=/bin/true (code=exited, status=0/SUCCESS)
Process: 3399 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 3399 (code=exited, status=0/SUCCESS)
Sep 16 18:48:13 pc systemd[1]: Starting PostgreSQL RDBMS...
Sep 16 18:48:13 pc systemd[1]: Started PostgreSQL RDBMS.
I get this error:
(base) marco@pc:~$ sudo su -l postgres postgres@pc:~$ psql psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
I already tried the suggestions found here: https://askubuntu.com/questions/50621/cannot-connect-to-postgresql-on-port-5432 withour any success
When commenting the hostssl line in pg_hba.conf:
# Only for SSL connections :
#hostssl all all cert
The error disappears:
(base) marco@pc:~$ sudo /etc/init.d/postgresql reload
[ ok ] Reloading postgresql configuration (via systemctl):
postgresql.service.
(base) marco@pc:~$ sudo service postgresql restart
(base) marco@pc:~$ sudo service postgresql status
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled;
vendor preset: enabled)
Active: active (exited) since Mon 2019-09-16 18:52:37 CEST; 3s
ago
Process: 3455 ExecReload=/bin/true (code=exited, status=0/SUCCESS)
Process: 3511 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 3511 (code=exited, status=0/SUCCESS)
Sep 16 18:52:37 pc systemd[1]: Starting PostgreSQL RDBMS...
Sep 16 18:52:37 pc systemd[1]: Started PostgreSQL RDBMS.
(base) marco@pc:~$ sudo su -l postgres
postgres@pc:~$ psql
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
Type "help" for help.
postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=#
So, my question is : how to correctly set ssl in /etc/postgresql/11/main/pg_hba.conf ?
SOLVED: adding the IP addresses range in pg_hba.conf:
hostssl all all 192.168.1.0/24 cert
