Cannot run psql in postgresql 9.5

Question

I am using Postgresql 9.5 on Ubuntu 16.04 LTS. I receive the below error when I type psql: a

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"?

On checking the logs in /var/log/postgresql/postgresql-9.5-main.log, I see the error as:

2018年11月26日 13:17:41 IST [3508-1] FATAL: could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied

Below are the permissions of etc/ssl/private and ssl-cert-snakeoil.key files:

vivek@vivek-ThinkPad-E480:~$ ls -l /etc/ssl
total 36
drwxr-xr-x 2 root root 20480 Nov 22 13:06 certs
-rwxr-xr-x 1 root root 10835 Dec 8 2017 openssl.cnf
drwxr--r-- 2 root ssl-cert 4096 Nov 22 13:06 private
vivek@vivek-ThinkPad-E480:~$ sudo ls -l /etc/ssl/private
total 4
-rw-r----- 1 root ssl-cert 1704 Nov 22 13:06 ssl-cert-snakeoil.key

Can anyone guide me in the right direction in setting permissions?

UPDATE #1: postgres user is also added to the group ssl-cert.

vivek@vivek-ThinkPad-E480:~$ getent group ssl-cert
ssl-cert:x:112:postgres

UPDATE #2: I found that there is no server.key present in /var/lib/postgresql/9.5/main.

Answer 1

The permissions on "/etc/ssl/private" should be "drwx--x---" (aka "0710"), which should have been the permissions it came with. I don't know it got changed. You could restore it with:

sudo chmod 0710 /etc/ssl/private/

Although maybe there is also a way to do it with your package manager.

You don't need a server.key file in "/var/lib/postgresql/9.5/main", because postgresql is configured to look for it in "/etc/ssl/private/ssl-cert-snakeoil.key" instead. You can make or get your own cert and put in "/var/lib/postgresql/9.5/main", but then you will have to change the configuration to look for it there.

• nice_dev: Hmm.. thanks for the info.. I would better prefer to keep it to refer to snakeoil.key instead then. Also, I ran your command both with and without -R. With -R, it tells me private key file "/etc/ssl/private/ssl-cert-snakeoil.key" has group or world access 2018年11月26日 18:55:21 IST [14299-2] DETAIL: File must have permissions u=rw (0600) or less if owned by the database user, or permissions u=rw,g=r (0640) or less if owned by root. Without -R, it tells me could not load private key file.
• nice_dev: I did restart the service using sudo service postgresql restart.
• jjanes: I don't understand that one. The file itself is owned by root and does have 0640 or less (or at least it did at the time you showed it to us--maybe it change since then). After you ran chmod with -R, what did you do to undo that?
• nice_dev: It didn't understand it either. Also, I first did without -R and then with -R. This way, the snakeoil permission was not tampered. Anyways, I got an answer on SO and I just realized that the permission for ssl-cert had to 5 instead of 4 and 0 for others. That's what the answer I received here does. Thanks for your time :)

Answer 2

1. View detail log and change permission to 0600. It work with me

DETAIL: File must have permissions u=rw (0600) or less if owned by the database user, or permissions u=rw,g=r (0640) or less if owned by root.