2

I am developing an application that will be run by untrusted users in high performance computing centers. My users will not have root access, therefore VPN is not an option.

I need some way to safely access a remote database (postgresql). I am aware that ssh can act as a tunnel however since I have untrusted users I do not think that using ssh is the correct option. This leads me with my last option of wanting to use TLS with client, server certificates signed by my own CA.

Assuming proper configuration, is TLS itself a secure option for restricting access to a database (postgresql)? Would it now be safe to expose the database to the internet? I figure that TLS would hide what service is at the port and only users with the proper client certificate will be able to connect.

  • 3
    .... The real issue I see here is whether you want to give untrusted users direct access to postgresql - I have a hard time believing that won't end in tears. Does providing dumps satisfy what you need? – pacifist Sep 29 '16 at 2:06
  • Ditto @pacifist. That said... It would help if you give more information. By "VPN is not an option", I assume you mean IPsec VPN. There are other types. SSL VPN is one. You haven't mentioned why you would need VPN (for example, perhaps the encryption terminator is a gateway instead of the destination?), and suggested TLS, so I'll assume you don't need further transport once it's decrypted...? – Courtney Schwartz Sep 29 '16 at 2:33
1

Yes... and no... and possibly yes again, if you take additional precautions.

Why Not

As far as authenticity and confidentiality is concerned, a TLS connection is perfectly adequate. But that's not the only reason people put services like Postgres behind a VPN. The other problem is that you may not want to expose your database to the public.

The VPN authentication serves as a second barrier to attackers in addition to your database's built-in auth. If postgres is compromised or flawed in some way, you're still reasonably safe if your VPN holds.

But if you expose your database to the Internet through a standard TLS connection, then anyone can attack it directly, exploiting any potential flaws in the native protocol or authentication, or consuming resources in a denial-of-service attack.

How to Make it Safe Again

But exposing services directly through TLS is actually done in high-security environments with one additional caveat: you have to authenticate the user BEFORE exposing them to the backend server.

The simplest way to do this is with client certificates. You're already setting up your own CA to sign your server certificate. If you use it to also sign client certificates, and require a signed client certificate in order to connect, then you've effectively re-established your VPN on a per-connection basis.

Setting up the server and client to use client certificates takes a small amount of work, but it's well within reason, particularly in comparison to a VPN suite.

Such a solution is every bit as secure as a VPN, less complicated, and easier to deploy.

  • when you state that I need to authenticate the user BEFORE exposing them to the backend are you suggesting using a service like a reverse proxy? For example I am aware that I could setup an nginx reverse proxy with my own CA. This would effectively make nginx manage the hand shake for postgresql. I would of course re-encrypt the traffic between nginx and postgresql. – costrouc Sep 29 '16 at 11:54
  • @costrouc if the db can do cert-based auth then that's an option too, but a proxy is probably safer if it's done right. – tylerl Sep 29 '16 at 14:30
1

Yes, it's a perfectly reasonable option. It's what AWS RDS, Heroku Postgres, etc does.

Like any software PostgreSQL could have pre-authentication remote exploits. You should keep up to date on patches and have a plan to upgrade to new versions. Run on a non-default port and if possible limit the IP address ranges allowed.

But in the end it's not too different to exposing something else, like an apache server.

However:

  • PostgreSQL is session-based. It expects clients to stick around and doesn't like it if they vanish without warning or stop responding midway through doing work. It won't crash or fail, but it'll do unnecessary work and the vanished connection may tie up resources for a while.

  • Unless you're quite careful the PostgreSQL protocol does lots of round trips. You can mitigate this with PgJDBC's or nPgSQL's batch modes, and I submitted a patch to libpq to add a batch mode to it too. But this latency will hurt performance.

  • PostgreSQL does more work pre-auth than some systems, and pre-auth connections count against the limit on max number of connections. So it's somewhat susceptible to DoS.

The first two issues also apply if you're using a VPN, they're not unique to TLS-based use.

For these reasons it may be worth running an application server close to the PostgreSQL database and bundling up application data requests into structured forms with json/protobuf/whatever requests and responses. This is most useful when clients are widely scattered and may come and go at random.

If you're not going to do that, set short timeouts on everything in Pg, be prepared to cope with retries, try to use batching and try to bundle work into bigger queries rather than lots of small ones.

  • 1
    Exposing postgres to the internet is very different from exposing apache. Apache can deal with request floods, postgress can't. (Although you probably should have a reverse proxy in front in either case) – grochmal Sep 29 '16 at 2:47
  • 1
    @grochmal True. PostgreSQL will work harder to cope with DoS because its pre-auth workload is significantly heavier-weight and it has a low cap on maximum number of active backends. You're quite right that putting a pgbouncer or similar in front is not a bad idea. – Craig Ringer Sep 29 '16 at 2:54
  • Edited to add what I think are actually more important caveats. – Craig Ringer Sep 29 '16 at 2:59
  • @CraigRinger I get that using postgresql for negotiating the handshake could be a problem as it would be susceptible to DoS. What if I setup a reverse proxy with TLS (via nginx) to preauthorize the traffic? Thus postgresql would not be doing the initial handshake. – costrouc Sep 29 '16 at 11:44
1

How sensitive is the data? You didn't say. That makes it impossible to gauge what's an appropriate level of risk. That's crucial. "Secure enough" is always relative to the risk, and your budget for the project.

This sounds like one of those "prove something doesn't exist" situations. Logically, that's impossible to do. You can only prove when something does exist. So I'm not going to succeed. But regardless, I'll give a shot at convincing you that for typical data, "secure direct access to databases for untrusted users" does not really exist.

The connection is only part of the "secure access" problem. Let's assume you've dealt with all of the possible negotiation downgrade exploits and so on. Even if the connection is encrypted with a bug-free TLS crypto suite that's properly configured, what data are the untrusted users sending through or before the encrypted channel, including (as Craig Ringer mentioned) authentication? Are you sanitizing it before it reaches the database?

Patching is obviously required, but not remotely enough. For a database that could have people exploiting it at any time, including other users' data in the database, you'd probably want DAM (database auditing and monitoring) and application-aware IPS and DoS protection. There's a reason that best practice is to put the database behind a gateway of some kind, and not directly expose it like that.

Running on a non-default port is security through obfuscation. It won't stop anyone that knows how to run a port scanner and fingerprinting, and definitely not anyone that is targeting you. Since you don't trust your users, you probably should assume one is targeting you.

IP range limitations won't help either if you don't trust any user IPs. Trusted hosts are useful only for allowing trusted IPs, and blocking the rest... which in your case sounds like blocking every IP on the Internet. Not very useful.

Assuming you've dealt with all of that, what I would suggest is transactions, backups, and strongly encrypting each user's data inside the database with their own private keys that your server doesn't have access to, so that even if a malicious user compromised the database, a dump of other users' data would be basically useless to the attacker. Have a standby server VM ready to go at all times, and it wouldn't hurt to have detached storage so that you can periodically trash that VM and bring up a fresh one. That wouldn't prevent someone from owning the server, but would at least help to protect the data itself, and offer rapid incident response and recovery.

But that's before you even get into the details specific to PostgreSQL.

As you can see, stronger security gets expensive and time-consuming. If the data is cat doodles on napkins, it's probably not worth it. You'd be better served by a different access method.

0

Transport security like TLS, VPN, or SSH is used to secure trusted users when connecting through untrusted network. They cannot secure systems from untrusted users.

If you have untrusted users that need access to the database, the traditional way to do this securely is to wrap the database in an application layer. The application layer implements authentication (e.g. username/password, shared secret tokens, client certificates), authorizations, predefined queries and permissions (e.g. "who is allowed to run what queries with which parameter" or "who is allowed to modify what data under what conditions"), and the output format (e.g. JSON, CSV, HTML). Optionally, the application layer may also implement the user interface. In modern practice, this is usually a browser based Web Application or a Web API.

In some cases, you can implement this using stored procedures and restrict your database users to allow only calling stored procedures, but this can be quite complicated when you start needing complicated permissioning and output formats.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.