6 ms·

So this is cool, and can serve as a good cheatsheet for beginners. However, the question where I struggled the most: how the hell do I set up a secure Postgres

by 2mol 6y ago

So this is cool, and can serve as a good cheatsheet for beginners. However, the question where I struggled the most: how the hell do I set up a secure Postgres instance on some cloud VM or server? (I _really_ love those 2.50 bucks per month instances) I spent a fair amount of time reading up on it, but it's still really easy to make a mistake in your `pg_hba.conf` or somehwere else. I remember disabling password based login, and just authenticating over SSH, and my server still got compromised after a day or two. 100% my fault - I think it was related to `COPY FROM/TO` being able to run arbitrary commands because I didn't understand that `postgres` is regarded as a superuser by the database. I've been using managed services like Heroku Postgres and RDS ever since. Point is: it would be of huge value to have a clear (but complete) overview of how to configure a Postgres instance so that it's secure in the basic sense. If that isn't possible (or very hard) then please let's collectively tell beginners to just use managed instances.

williamjackson 6y ago
I launched the Postgres Docker image on a VPS several years ago, published the port on the open Internet, and ... used strong passwords? I still haven’t been compromised. Have I done something wrong?
- 2mol 6y ago
  Who knows! That's my struggle with security, unless you have good monitoring in place it's not even trivial to notice problems. In my case I was only made aware of the compromised VM because my hosting provider sent me very stern email about my server's IP netscanning their entire fricking address range.
  - rfoo 6y ago
    How about try setting up a box with exactly same steps that you would do for your database instance except actually installing the database? Yes, please include all the steps that you may consider "irrelevant without a database installation", they may well be important. Then see if it got compromised.
- capitol_ 6y ago
  I guess that the docker image doesn't use TLS for connections, or if it does it doesn't use certificates from a CA. This means that your data either goes in plain text over the network, or might be vulnerable to a MITM attack (if you don't manage your certs correctly). I also guess that you don't use SCRAM as the password hashing method, but rather MD5. That means that if someone is able to listen to the connection, they can do a replay attack using the password hash, as there is only 32 bits of entropy added to the hash from the server side. And once you have managed to do the replay attack you can issue arbitrary sql commands as that user.
  - wiredfool 6y ago
    I think it’s probably simpler than that, there are some docker Postgres image that are setup by default to trust connections. Which is ok until about 5 seconds after you open the port to the world.
deleted 6y ago
[deleted]
chousuke 6y ago
I'm not sure where you went wrong; by default, Postgres is secure; the configuration listens on localhost and even if you change that, does not allow network access to the superuser. Distributions may ship with less secure configurations but I'm not aware of any that do. I'm also pretty sure that the upstream documentation warns against using the superuser for application access, so if you just create a regular database user protected by a reasonable password it will be as secure as any database exposed to a network can be; of course, exposing databases to the internet is something to be avoided in the first place. non-mobile EDIT: The above ignores TLS, which is generally a good idea if you want to make something accessible over the network. A guide for beginners might be useful, but if you work with these things, it may be useful to learn how to approach security in general so that you will be able to learn how to secure anything, or at least know when you don't know enough. In general, installing services securely requires the administrator to understand how the service is accessed by legitimate users and whether in doing so is potentially exposed to external access. If you just google for how-tos you're quite likely to find lots of bad advice that skips security considerations and takes you from A to B the fastest route. The effort required is entirely dependent on your requirements; in most cases, avoiding exposure to the internet, patching your software and using strong passwords is enough, as it stops nearly all low-effort automated attacks. For starters with any network-exposed service, you should understand that not exposing it to the internet in the first place means that the rest of your security measures will be challenged less; so if you can, limit access to internal networks and specific hosts with firewalls and ACLs. If you have to expose a service to the internet, then you need authentication and authorization; anyone will be able to connect to the service, but the service should challenge them to identify themselves using secure credentials. Once the user has access to your service, you'll want to limit what they can do with it. This requires reading the manual. Lastly, you'll generally want to keep your software up-to-date; unpatched software may have bugs that allow attackers to bypass some of the security measures you have set up.
- deleted 6y ago
  [deleted]
- istjohn 6y ago
  > A guide for beginners might be useful, but if you work with these things, it may be useful to learn how to approach security in general so that you will be able to learn how to secure anything, or at least know when you don't know enough. Any suggestions on how to get started with this?
Twisell 6y ago
Before anyone can reasonably answer this (and maybe dba.stackexchange.com would be a better place to ask) can you 100% confirm that the issue can't come from your ssh setup? > I remember disabling password based login, and just authenticating over SSH, and my server still got compromised after a day or two. This sentence in particular is hard to parse. Did you turn off password login in pg_hba.conf or in your sshd_config ? PS: VPS box literally start being scanned/tested over standard ssh port minutes after being spin-up. It seem less frequent however to be attacked for specific postgreSQL vulnerabilities (but that might well be a new trend).
- R0b0t1 6y ago
  >VPS box literally start being scanned/tested over standard ssh port minutes after being spin-up. Specifically it seems to be giving them a domain name; I've had instances sit with few or no attempts against them as long as I do not assign a name.
  - stu2b50 6y ago
    At least on digital Ocean I've definitely had weird post requests to /php/admin or whatever even before I've added a domain name.
dillonmckay 6y ago
Do you whitelist IPs for the Postgres port? It should not be publicly accessible to the world. You could also tunnel it over ssh and map the port.
- ramraj07 6y ago
  At least in AWS tunneling through SSH suddenly makes it not cheap anymore - I have currently gone with the whitelisted IP list for personal projects.
orev 6y ago
Have you read the official docs on the Postgres site? Most of that stuff is covered there. Not everything has to be in a blog post when the official docs already cover it.
ramraj07 6y ago
There ARE _almost_ clear guides about how to setup an RDS instance (and a basic webserver if you want) including networking, from AWS itself, it's just these guides are buried under teams of SEOed listicle crap and mediocre medium posts with no information. Will find and update this post.
- css 6y ago
  I assume you mean this: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_... For me, it's the third result on DuckDuckGo for "PostgreSQL RDS"
siscia 6y ago
But why you don't just use a managed service? Too expensive? Not as fun? Asking as I am offering a similar service, hosted database. simplesql.redbeardlab.com (not production ready)
- js4ever 6y ago
  Your site is down ... Clearly not production ready :) Btw, I like what you did with redisql.
- marcosdumay 6y ago
  My experience is that managed services are either way too expensive or badly managed enough that doing anything different from the basics is a endless source of problems. That "or" is not exclusive.
latchkey 6y ago
For about $7-8/month, you get a managed one on GCP with Cloud SQL. It isn't perfect for all use cases, but has been fantastic in terms of developing out a small app.
maxmalysh 6y ago
Pro tip: store configuration files in git.

by 2mol 6y ago

williamjackson 6y ago

I launched the Postgres Docker image on a VPS several years ago, published the port on the open Internet, and ... used strong passwords? I still haven’t been compromised. Have I done something wrong?

2mol 6y ago
Who knows! That's my struggle with security, unless you have good monitoring in place it's not even trivial to notice problems. In my case I was only made aware of the compromised VM because my hosting provider sent me very stern email about my server's IP netscanning their entire fricking address range.
- rfoo 6y ago
  How about try setting up a box with exactly same steps that you would do for your database instance except actually installing the database? Yes, please include all the steps that you may consider "irrelevant without a database installation", they may well be important. Then see if it got compromised.
capitol_ 6y ago
I guess that the docker image doesn't use TLS for connections, or if it does it doesn't use certificates from a CA. This means that your data either goes in plain text over the network, or might be vulnerable to a MITM attack (if you don't manage your certs correctly). I also guess that you don't use SCRAM as the password hashing method, but rather MD5. That means that if someone is able to listen to the connection, they can do a replay attack using the password hash, as there is only 32 bits of entropy added to the hash from the server side. And once you have managed to do the replay attack you can issue arbitrary sql commands as that user.
- wiredfool 6y ago
  I think it’s probably simpler than that, there are some docker Postgres image that are setup by default to trust connections. Which is ok until about 5 seconds after you open the port to the world.

deleted 6y ago

[deleted]

chousuke 6y ago

I'm not sure where you went wrong; by default, Postgres is secure; the configuration listens on localhost and even if you change that, does not allow network access to the superuser. Distributions may ship with less secure configurations but I'm not aware of any that do. I'm also pretty sure that the upstream documentation warns against using the superuser for application access, so if you just create a regular database user protected by a reasonable password it will be as secure as any database exposed to a network can be; of course, exposing databases to the internet is something to be avoided in the first place. non-mobile EDIT: The above ignores TLS, which is generally a good idea if you want to make something accessible over the network. A guide for beginners might be useful, but if you work with these things, it may be useful to learn how to approach security in general so that you will be able to learn how to secure anything, or at least know when you don't know enough. In general, installing services securely requires the administrator to understand how the service is accessed by legitimate users and whether in doing so is potentially exposed to external access. If you just google for how-tos you're quite likely to find lots of bad advice that skips security considerations and takes you from A to B the fastest route. The effort required is entirely dependent on your requirements; in most cases, avoiding exposure to the internet, patching your software and using strong passwords is enough, as it stops nearly all low-effort automated attacks. For starters with any network-exposed service, you should understand that not exposing it to the internet in the first place means that the rest of your security measures will be challenged less; so if you can, limit access to internal networks and specific hosts with firewalls and ACLs. If you have to expose a service to the internet, then you need authentication and authorization; anyone will be able to connect to the service, but the service should challenge them to identify themselves using secure credentials. Once the user has access to your service, you'll want to limit what they can do with it. This requires reading the manual. Lastly, you'll generally want to keep your software up-to-date; unpatched software may have bugs that allow attackers to bypass some of the security measures you have set up.

deleted 6y ago
[deleted]
istjohn 6y ago
> A guide for beginners might be useful, but if you work with these things, it may be useful to learn how to approach security in general so that you will be able to learn how to secure anything, or at least know when you don't know enough. Any suggestions on how to get started with this?

Twisell 6y ago

Before anyone can reasonably answer this (and maybe dba.stackexchange.com would be a better place to ask) can you 100% confirm that the issue can't come from your ssh setup? > I remember disabling password based login, and just authenticating over SSH, and my server still got compromised after a day or two. This sentence in particular is hard to parse. Did you turn off password login in pg_hba.conf or in your sshd_config ? PS: VPS box literally start being scanned/tested over standard ssh port minutes after being spin-up. It seem less frequent however to be attacked for specific postgreSQL vulnerabilities (but that might well be a new trend).

R0b0t1 6y ago
>VPS box literally start being scanned/tested over standard ssh port minutes after being spin-up. Specifically it seems to be giving them a domain name; I've had instances sit with few or no attempts against them as long as I do not assign a name.
- stu2b50 6y ago
  At least on digital Ocean I've definitely had weird post requests to /php/admin or whatever even before I've added a domain name.

dillonmckay 6y ago

Do you whitelist IPs for the Postgres port? It should not be publicly accessible to the world. You could also tunnel it over ssh and map the port.

ramraj07 6y ago
At least in AWS tunneling through SSH suddenly makes it not cheap anymore - I have currently gone with the whitelisted IP list for personal projects.

orev 6y ago

Have you read the official docs on the Postgres site? Most of that stuff is covered there. Not everything has to be in a blog post when the official docs already cover it.

ramraj07 6y ago

There ARE _almost_ clear guides about how to setup an RDS instance (and a basic webserver if you want) including networking, from AWS itself, it's just these guides are buried under teams of SEOed listicle crap and mediocre medium posts with no information. Will find and update this post.

css 6y ago
I assume you mean this: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_... For me, it's the third result on DuckDuckGo for "PostgreSQL RDS"

siscia 6y ago

But why you don't just use a managed service? Too expensive? Not as fun? Asking as I am offering a similar service, hosted database. simplesql.redbeardlab.com (not production ready)

js4ever 6y ago
Your site is down ... Clearly not production ready :) Btw, I like what you did with redisql.
marcosdumay 6y ago
My experience is that managed services are either way too expensive or badly managed enough that doing anything different from the basics is a endless source of problems. That "or" is not exclusive.

latchkey 6y ago

For about $7-8/month, you get a managed one on GCP with Cloud SQL. It isn't perfect for all use cases, but has been fantastic in terms of developing out a small app.

maxmalysh 6y ago

Pro tip: store configuration files in git.