Loads of PostgreSQL systems are sitting on the internet without SSL encryption

Only a third of PostgreSQL databases connected to the internet use SSL for encrypted messaging, according to a cloud database provider.

Bit.io, which offers a drag-and-drop database as a service based on PostgreSQL, searched shodan.io to create a sample of 820,000 PostgreSQL servers connected to the internet over September 1-29. Of this sample, more than 523,000 PostgreSQL servers did not use SSL (64 percent).

The company said this left open the possibility for outsiders to snoop on the data transmitted to and from the server. It also noted 41 online PostgreSQL servers did not even require a password.

"When you connect to a website through your web browser, data you send and receive is probably encrypted," bit.io CTO Jonathan Mortensen said in a blog post. "It's amazing, then, that data sent to and from Internet-connected PostgreSQL servers is very likely unencrypted. It's a problem."

The company also conducted an informal survey of 22 popular SQL clients. It found only two require encrypted connections by default, while six will ask for encryption but silently accept an unencrypted connection. The rest are unencrypted by default, and require opt-in to using SSL.

Also discovered was that more than 43 percent of those with SSL certificates were self-signed. This means that while they are encrypted, the certificates often do not confer trust as they may not be issued or validated by a certificate authority, the company said. Meanwhile, 4 percent of the certificates had expired.

• PostgreSQL 15 promises to ease Oracle and SQL Server migrations
• Cockroach Labs CTO: Google became too comfortable, I wasn't being challenged
• Google previews streaming connector for BigQuery
• Microsoft offers SQL Server 2022 release candidate to Linux world

EDB, a consultancy specialized in building and supporting PostgreSQL systems, pointed out that it was the minority of PostgreSQL databases that were connected to the internet, and that the open source system does not accept connections from the internet by default.

Marc Linster, EDB CTO, told The Register: "It seems that some database hosting providers are doing their customers a disservice by not giving them the ability to provide a restricted network access list."

The recommended approach was to give customers the option of an allow list to restrict network access, he said.

Linster pointed out that PostgreSQL has a built-in firewall of sorts called the pg_hba.conf. Since external connections are disallowed by default, if developers need to open up PostgreSQL to the internet, they should do it via an app server that sits within a corporate firewall.

He also said that production databases should have valid SSL certificates. The pg_hba.conf allows you to restrict traffic to only SSL connections by simply changing host to hostssl, as mentioned in the blog.

DBAs who manage PostgreSQL should review the firewall settings to ensure that connections are restricted to application servers and that the connection is hostssl, so only SSL connections can be made, Linster said.

For database-as-a-service (DBaaS), DBAs and developers (or any decision maker) should ensure the hosting provider allows the customer to provide an allow list and supports Bring Your Own Certificates (BYOC). ®