Ssl – PSQL error with restarting postgres in SSL

opensslpostgresqlpsqlssl

I set up server.crt, root.crt and servery.key in postgresql (using http://howtoforge.com/postgresql-ssl-certificates for instructions), set ssl = on and restarted the service successfully.

However, as I test the connection with psql to see if it's running in SSL I get the following

-bash-3.2$ psql -h localhost -U dbadmin
psql: could not open certificate file "/var/lib/pgsql/.postgresql/postgresql.crt": No such file or directory

I have the certificates stores in pgsql/data/, where they should be, so what's the disconnect between the top example and the following one?

psql ssl test documentation

Best Answer

This is looking for the client certificate files. The server certificate files belong in pgsql/data/, as you correctly pointed out. The client certificate files are looked for in $HOME/.postgresql/. You are probably logged in as the postgres user, who happens to have a home directory of /var/lib/pgsql/, so that's how you got that path.

So either put the client certificate files where they are looked for, or log in as the correct user, or reconfigure the server so that it doesn't require client certificates.

Related Solutions

PostgreSQL – Default Superuser Username/Password After New Install

CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!

This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.

What you should do is follow Chris James's answer:

sudo -u postgres psql postgres

# \password postgres

Enter new password:

To explain it a little bit. There are usually two default ways to login to PostgreSQL server:

By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql. Note that sudo -u does NOT unlock the UNIX user.
by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).

So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.

Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.

Ubuntu get PostGreSQL running

You can know the status of your Postgres server via the command

sudo /etc/init.d/postgresql-8.3 status

To start it you can issue the command

sudo /etc/init.d/postgresql-8.3 start

and to stop you can issue the command

sudo /etc/init.d/postgresql-8.3 stop

Best Answer

Related Solutions

PostgreSQL – Default Superuser Username/Password After New Install

Ubuntu get PostGreSQL running

Related Topic