CODEWITHSUNDEEP
postgresql
user2889419
user2889419

Reputation:

PostgreSQL local connection advice

Okay we have got a VPS Linux machine, and we are going to install a PostgreSQL guy as datasource there.

We will not change any default connection setting of course, and pg will accept only local connections.

So the question is, should we have secured(SSL) connection with postgresql while there will be no any IP accepted except localhost?

Upvotes: 0

Views: 91

Answers (2)

Daniel Vérité
Daniel Vérité

Reputation: 61526

It's not clear why SSL on localhost would be desirable.

On the other hand, performance is a good reason not to use it when retrieving large amounts of data. Here's a small demo.

I'm fetching a single row of 4.64 MB from a table bintest(b bytea).
Doing this through unencrypted Unix domain socket takes about 25ms:

CPU: Intel(R) Xeon(R) CPU E31230 @ 3.20GHz (which apparently includes AES instructions).

$ psql -d demoml
psql (9.3.4)
Type "help" for help.

demoml=> \o /dev/null
demoml=> \timing
Timing is on.
demoml=> select * from bintest;
Time: 26.713 ms
demoml=> select * from bintest;
Time: 25.613 ms

Now the same with encrypted localhost:

$ psql -h localhost -d demoml
Password for user manitou: 
psql (9.3.4)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

demoml=> \o /dev/null
demoml=> \timing
Timing is on.
demoml=> select * from bintest;
Time: 464.545 ms
demoml=> select * from bintest;
Time: 462.927 ms

In this test, TCP+SSL is about 460/18 = 18x slower than unencrypted Unix domain socket (which is not significantly different than unencrypted localhost).

In other tests, I've also timed the connection establishing between PostgreSQL and a libpq client, and it was about 5x slower with SSL.

Upvotes: 1

ArtemGr
ArtemGr

Reputation: 12547

You should ask yourself, what kind of attacks are you protecting the PostgreSQL from?

Are you ready to protect the server machine from being compromised or from somebody having a physical access to it? If yes, then you need much more than SSL encryption. If not, then you have to assume that the local machine is relatively safe and there is no need to encrypt there. See also this answer.

Generally, there's no need to encrypt the local connection. Primary purpose of encrypting connections is to protect your data (and passwords) from somebody with access to a server or a line between your PostgreSQL server and a client.

Upvotes: 1

Related Questions