>By default, PostgreSQL listens on a TCP port 5432.<p>This post seems to outright state that by default postgres is listening to everyone via TCP for connection.<p>This is not true.<p>Unless you edit pg_ident.conf, your postgres install will not listen for connections outside of on localhost. So, while it's correct to say that it listens to TCP port 5432, there is a very narrow limit to whom it's listening for. Namely, the same machine.<p>Postgres is pretty secure by default. It doesn't allow external connections. It also requires a username and password to connect with permissions on the database you're connecting to.<p>Compare that to something like redis. They at least used to listen for connections external by default and not even have a password to connect. I can imagine it's still very easy to find all kinds of interesting stuff snooping around on port 6379.
> Unfortunately, this sort of air-gapped setup is not something PostgreSQL supports out-of-the-box.<p>Is a reverse tunnel really air gapped?<p>I thought AG meant one had to physically touch the device and transfer using devices without any network capability, such as a flash drive?
I personally secure my postgres instances by putting them in a silod vpc without internet access. I then limit incoming connections to only be allowed from the separate vpc containing the application server<p>I then use a bastion host when I need to access ssh on the instance. The bastion host remains off and inaccessible except for when I need to perform maintenance.<p>The advantages of this is that there is no "always-open" access to the instance.<p>Not sure why the author does not advocate this.
A good overview, but the most secure way is to not give any table-level access privileges and only allow access using SECURITY DEFINER stored procedures (the PG equivalent of the setuid bit). For instance if you have a table of users with login and hashed salted password, you could implement a check_password() procedure. If the app account is compromised, it would not have access to the password hashes or even be able to enumerate users.
It feels weird to me that this blog post would suggest acquiring Let's Encrypt certificates for certificate encryption. While it's great for something public facing that needs your CA installed by default, creating certificates for things that you probably don't want being public - like your database backend - just makes it more discoverable. For example, certificate transparency logs mean that anybody can see what certificates Let's Encrypt has handed over to you. Example: <a href="https://crt.sh/?q=ycombinator.com" rel="nofollow">https://crt.sh/?q=ycombinator.com</a><p>Of course, this is a security through obscurity type of approach, and you'd want to secure your database whether or not somebody knew where it was running. But there's a difference between somebody seeing that you've just created `staging-psql.foo.com` that you might still be configuring, and the passive background noise of internet port scanning that's a little less targeted.
This is surely a silly question (and probably a lot of devops will think I am an idiot), but what do you recommend to secure a PostgreSQL that is accesed by different IPs? We use Azure PostgreSQL database, which I guess they take care of most of the security, but they allow us to set up firewall rules, as only allowing connectins by established IPs.<p>My (our) problem is that we use a lof ot AWS lambda functions that read and write to the database, and they always execute from different (dynamic) IPs, so what is the best solution in this case?
Seems the old adage of "beware of blog posts bearing gifts" strikes again.<p>For blog post masquerading as a how-to on securing Postgres, the fact the article only fleetingly mentions the word "function" once is not cool.<p>One of the biggest things you can do for Postgres (or any database for that matter) is enforce the use of stored procedures ("functions" in PG-speak) rather than direct SQL queries.<p>SQL Injection attacks are common as muck. Stored procedures are a quick and easy way to mitigate them.<p>Stored procedures also have the added bonus of allowing the DBA to remain in control and ensure a higher quality of SQL query rather than upstream devs sending all manner of unoptimised SQL queries.
I see quite a few posts discussing running Postgres in a separate VPC or network.
We currently run our Postgres databases within Kubernetes and leverage network policies to ensure that only application pods can access the database.<p>The application pods ingest the db credentials from Vault. The biggest concern we have today is automating credential rotation.<p>Curious if anyone else has a similar setup or thoughts on ours?
What exactly is the security benefit of a reverse tunnel, in comparison to just listening on a port?<p>Usually DB servers are "always on" and always accept connections, so the reverse tunnel also needs to be always up.<p>Regarding row-level security: that sounds quite awesome, but in the form it's described in the article, very limited in the number of use cases.<p>Quite often you have a web app that talks to the DB and that uses a service account. So as with, with row-level security you can just allow or disallow things to the service account, not to the user logged into the web application.<p>Is there a way to drop from the service account into a less-privileged role inside a transaction or so?
Possibly stupid questions.<p>(1) When is operating your own PostgreSQL instance desirable?<p>(2) Isn’t this equivalent to running an RDS instance in its own VPC with public access turned off and only allowing comms with app VPC?
Teleport looks cool.
We have a pretty elaborate home grown bastion host setup. Seems like an area that should have more competition. Especially after looking at teleports pricing.
Is there any reason <i>against</i> using wireguard to prevent any external connections to postgres <i>if</i> both the postgres clients and server are under your full control (e.g. only your server connect to postgres)?
<a href="https://supabase.io/docs" rel="nofollow">https://supabase.io/docs</a> is a good example of adding a nice security later (and more) on top of postgres