11 ms·

Securing a Postgres Database

312 points by bryanmikaelian 6y ago

greatjack613 6y ago
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 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. The advantages of this is that there is no "always-open" access to the instance. Not sure why the author does not advocate this.
- polskibus 6y ago
  How do you monitor node health?
  - duckfang 6y ago
    You can put other machines with a highly restrictive set of network rules that allow data to cross inside the network and outside the network in very controlled ways. Email is one such way.
    - capableweb 6y ago
      > Email is one such way. I'm sorry but could you clarify? You mean that you monitor and/or collect data from hosts inside of a network via email somehow?
    - abhishekjha 6y ago
      I am curious. Most of the monitoring stacks include something like telegram/Prometheus to collect metrics and send to influxdb. How would you do this via email?
      - duckfang 6y ago
        Inside the he bastion network/VPC, there can be logging, monitoring, ad/kerb+openldap stuff to do the requisite thing. And your logging and monitoring can send trigger emails when they catch bad things. Your inbox needs to be an automatable inbox, say controlled by bleeder or another flowengine. From there you can build a full messaging dashboard and have it done in something that could pass a very stringent audit.
- deleted 6y ago
  [deleted]
- duckfang 6y ago
  Yep, and your answer is the better default answer. Bastion<->app host<->DB Bastion can talk to the net. App host can talk outbound but inbound only accepts bastion and DB. DB can only talk with app host. Obviously, you harden everything appropriately... But with this arrangement, it's very difficult to penetrate this sort of network. Think of it as a network that as a whole is default-deny.
- chatmasta 6y ago
  > Not sure why the author does not advocate this. Probably because the blog post is an advertisement for their product, which already allows you to implement bastion hosts as you describe. From the bottom: > Databases do not need to be exposed on the public Internet and can safely operate in air-gapped environments using Teleport’s built-in reverse tunnel subsystem.
  - greatjack613 6y ago
    Did not catch that. Sneaky self promotion there :) Not sure what there reverse tunnel product is, but a bastion host is super easy to implement, just spin up an ec2 and walla. Curious as to what value they are providing
    - chatmasta 6y ago
      They've been around a while, it actually seems quite cool. Bastion hosts are simple to setup, sure, but Teleport adds a whole bunch of porcelain on top, e.g. integration with SSO, web UI for administration, etc. Haven't used them myself but I wouldn't be against trying it if in the market for something like that.
      - duckfang 6y ago
        I find that porcelain has a very bad time in breaking when you least suspect it. The same is true for server applications that have weird 3rd party dependencies that may go down when you least suspect it.
    - jdmichal 6y ago
      Unsure whether you meant walla, but just in case this is what you meant: It's "voila", from French "et violà". https://www.collinsdictionary.com/dictionary/french-english/et-voil%C3%A0 https://www.collinsdictionary.com/dictionary/french-english/...
- zomgwat 6y ago
  One alternative to keeping the bastion off is to restrict access to the bastion by IP address. Ideally, the list of IP addresses that need access to the bastion should be relatively small. In cloud environments, it's straightforward to update network firewall rules as IP addresses change. Residential and office IP addresses don't change much so it's not much of a hassle in my experience. That said, it can get annoying if you find your self working on a network that rotates your IP address frequently (e.g., a hotspot).
- brightball 6y ago
  Agreed. What would be the best way when using something like Heroku?
  - fmajid 6y ago
    Something like Nebula (https://github.com/slackhq/nebula https://github.com/slackhq/nebula) or Tailscale, perhaps?
    - brightball 6y ago
      Can you setup Nebula on your Heroku dynos and an RDS instance?
- 120bits 6y ago
  Currently I have a postgres/timescaledb running on EC2 in VPC which has no internet access. I do VPN tunnel to access private local subnet and have security group settings that allows my and 1 more machine access only. I usually have a jump server that I use, but I don't want to keep my ssh keys on the server or leave it behind.
  - clan 6y ago
    If the jumpserver is trusted and controlled by you then you should have a look at ssh agent forwarding. Then you avoid leaving keys on the jumphost.
    - ylk 6y ago
      A better alternative would be ProxyJump. See e.g. https://serverfault.com/questions/958222/bastion-server-use-tcp-forwarding-vs-placing-private-key-on-server https://serverfault.com/questions/958222/bastion-server-use-... Edit: To add some details - using ProxyJump you don’t have to expose anything to the jump host and instead just proxy through it.
- lovedswain 6y ago
  What value is added by using a separate VPC? Equivalent restrictions can more easily be done with security groups, including on the outbound networking
  - cj 6y ago
    I would suggest considering segregation by subnets (in addition to security groups), using public / private subnets, where any server in a private subnet (behind a nat gateway) doesn't/can't have a public ip address, and therefore cannot be accessed via the public internet. You could then use a bastion to access servers in the private subnet, or use something like AWS Session Manager which provides command line access via web browser in lieu of a bastion.
    - lovedswain 6y ago
      > What value is added by using a separate VPC? Adding more mechanisms on top is pointless when the effort could be invested in, for example, automated auditing of SGs, which is vastly more potent from a hardening perspective than adding additional layers of technical redundancy that are still exposed to the same flawed human processes. When you reach a team of 10-20 folk on a project, stuff tends to get confusing and/or lazy with elaborate configurations. Security design therefore is about more about managing that outcome through simplicity and process hardening than.. well.. I don't even know what threats a separate VPC protects against
      - cj 6y ago
        > When you reach a team of 10-20 folk on a project, stuff tends to get confusing From the perspective of maintaining security in the least confusing way possible in a team setting, I could see a scenario where you have a VPC that only has private subnets, and no public subnets at all. You could call it “Database VPC” and assuming your team doesn’t reconfigure the VPC to add public subnets, you can be comfortable with your team adding more EC2 servers / databases / whatever in that VPC since they would all be in private subnets inaccessible by the public internet. And then you could have a 2nd VPC with private/public subnets that are less locked down than the database VPC, which might contain your load balancers, application servers, etc. I suppose the benefit would be the logical separation of databases into a VPC without any public subnets. And the only way to gain access to subnets in that VPC would be through VPC peering. (The above assumes you’re using AWS). Although after typing all that, I still think you can accomplish a comparably secure (on a network level) architecture using security groups, or even separate subnets, without separate VPCs. In general I try to avoid multiple VPCs because VPC peering in AWS can get tricky (or impossible if both VPCs have overlapping CIDR blocks).
- moltar 6y ago
  I use almost the same approach. I also limit access to just one IP, which is a VPN server hosted on a different cloud provider. You can run a bulletproof VPN server easily using something like algo (https://github.com/trailofbits/algo https://github.com/trailofbits/algo)
Dowwie 6y ago
Check out using Hashicorp Vault for dynamic postgres credentials and securely obtaining them during system init
throwaway13337 6y ago
>By default, PostgreSQL listens on a TCP port 5432. This post seems to outright state that by default postgres is listening to everyone via TCP for connection. This is not true. 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. 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. 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.
- deleted 6y ago
  [deleted]
- postgressomethi 6y ago
  > Unless you edit pg_ident.conf, your postgres install will not listen for connections outside of on localhost. I don't know what the defaults are, but pg_ident.conf has absolutely nothing to do with this. The main configuration file (I think postgresql.conf usually) has listen_addresses, which controls the addresses on which postgres listens, as you might guess. pg_hba.conf (not pg_ident.conf) controls the authentication methods the server asks from the client, depending on how they're connecting.
  - Quekid5 6y ago
    You're technically correct, but the difference is immaterial unless we're talking about outright pre-auth exploits. Or exploits of the auth itself, I guess. (I think I recall exactly one in the history of PostgreSQL since I started using it, but it is what it is.)
- hankchinaski 6y ago
  >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 Not anymore since version 3.2.0 [0] >Unfortunately many users fail to protect Redis instances from being accessed from external networks. Many instances are simply left exposed on the internet with public IPs. For this reasons since version 3.2.0, when Redis is executed with the default configuration (binding all the interfaces) and without any password in order to access it, it enters a special mode called protected mode. In this mode Redis only replies to queries from the loopback interfaces, and reply to other clients connecting from other addresses with an error, explaining what is happening and how to configure Redis properly. [0] https://redis.io/topics/security https://redis.io/topics/security
megous 6y ago
Removing the public schema and not allowing users to connect to all databases by default is also a good idea.
perlgeek 6y ago
What exactly is the security benefit of a reverse tunnel, in comparison to just listening on a port? Usually DB servers are "always on" and always accept connections, so the reverse tunnel also needs to be always up. 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. 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. Is there a way to drop from the service account into a less-privileged role inside a transaction or so?
- old-gregg 6y ago
  Disclaimer: I work at Teleport (but I am not the author of the article). This work was done because the Teleport users who used it for SSH kept asking for the same access for their databases. The reasoning goes like: 1. Setting up a single proxy gives you the same benefits for N databases as they come online. No need to manage additional endpoints (public IPs, ports, etc). 2. You have the same centralized place to manage auth/authz for all users. 3. This allows to connect to databases on the edge, where there isn't an opportunity to have a permanent public IP and locations frequently go online/offline. 4. Finally, it's nice to have unified visibility into what's available (for users) and centralized logging/audit for the security team. As always, all of this is possible with other tools. The world of open source is vast and full of options, but we were hoping to make it simpler, with less configuration and moving parts.
- hobs 6y ago
  I know in SQL Server a lot of products use the CONTEXT_INFO function for this, basically store some variable per connection and change it for context switching/row level security. Obviously you need to trust the service account enough to do that.
paulryanrogers 6y ago
> Unfortunately, this sort of air-gapped setup is not something PostgreSQL supports out-of-the-box. Is a reverse tunnel really air gapped? I thought AG meant one had to physically touch the device and transfer using devices without any network capability, such as a flash drive?
- tyingq 6y ago
  "Unfortunately, this sort of air-gapped setup is not something PostgreSQL supports out-of-the-box." Not sure what they mean by "out of the box", but you can make the listen_addresses list empty: "listen_addresses (string) ...If the list is empty, the server does not listen on any IP interface at all, in which case only Unix-domain sockets can be used to connect to it..." https://www.postgresql.org/docs/9.3/runtime-config-connection.html https://www.postgresql.org/docs/9.3/runtime-config-connectio...
  - jagged-chisel 6y ago
    I’m sure the article author meant, by saying “out-of-the-box,” the features available on initial, default install. I disagree with the author and agree that indeed a fresh install supports (i.e. allows one to configure without rebuilding) not talking over a network at all.
    - tyingq 6y ago
      Ah, yes, re-read, and they do mention this.
- gnabgib 6y ago
  That's a good question. I agree with your definition of AG. However, the only time a database (or any other kind of data store) would be useful in an AG setting would be if it's part of an AG system including network and other computers. Perhaps we need another somewhat similar term. It's like null-routing or firewalling devices on your network.. they're technically "connected" but if they cannot dial out they're in some ways gapped. (This is handy for dubious quality IoT devices, they can't phone home, auto-patch to drop features, or share your usage information with $corp, but still respond to local network commands). To some extent tunneling is security through obscurity (an SSH tunnel has moved the port you need to secure from 5432 to 22)
fmajid 6y ago
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.
- ed25519FUUU 6y ago
  Stored procedures are really not used enough. They’re incredibly useful, and not just for security. People can make out-of-band updates to the query, like making it more efficient or migrating it, without requiring any changes to the all.
  - doctor_eval 6y ago
    When used for business logic they also execute about 20x faster than the same logic encoded in a client, and in far fewer LOC. Getting rid of all those round-trips has a huge effect!
    - koolba 6y ago
      Running a slow hash operation that should take a non-negligible amount of CPU on your single core per process database server isn’t a great idea. That’s not a rag on stored procedures more generally. Just that specific use case scales poorly to a large number of operations as its inherently cpu bound.
      - tpetry 6y ago
        You can do the slow pbkdf2 hash on your webserver and use the result for your stored procedure to check the password. If the password column is only visible to the stored procedure you have used the best features of both systems.
        6y ago
indemnity 6y ago
Possibly stupid questions. (1) When is operating your own PostgreSQL instance desirable? (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?
- vilified 6y ago
  Some input on this would be greatly appreciated. For example: how much of this would I need not worry about if I use a managed postgres database (like from digitalocean or aws or any provider actually)
- paulryanrogers 6y ago
  1. When you don't want your data on other people's computers, or need extensions or configuration that's impractical, or need control over your uptime
- edoceo 6y ago
  I do it because of custom extensions we need. I don't think any cloud provider allow it.
- mvolfik 6y ago
  I'll be soon doing this for a project running on LAN without connection to internet, during events once COVID is over
- strzibny 6y ago
  1. Saving money 2. Need an extension that is not available 3. Running the whole app on a single node (to avoid network trips) 4. Want to learn
- pjungwir 6y ago
  Running your own Postgres is surprisingly easy, even with backups/monitoring/replication. Postgres replication has a reputation for being difficult to set up, but that's not what I've found. I think the reputation largely reflects how it was 15-20 years ago, not today. It's not completely turn-key I suppose, but there are many resources to learn. Speaking of monitoring: there are far fewer options if you're on RDS. Personally my favorite is munin, where you can instantly see all kinds of stats & history at multiple levels of abstraction. There are many excellent Postgres plugins to report on transactions, locking, etc., and it's easy to write your own. On RDS you can't install custom extensions. They have a whitelist of the most commonly-used ones, but if you find a different one (or build your own), you're out of luck. This really hampers you if you want to get the most from your database. I will say though, building custom extensions can also block you from using CI/CD solutions (or at least make them harder to set up), since they may have similar restrictions. Writing a custom extension is sort of a last restort, but it can be a huge boost for certain problems. RDS also doesn't grant you direct access to the WAL. That means you can't use WAL-E/WAL-G (a really nice incremental backup solution) or many other helpful tools. You can't do replication except via AWS's own black-box features. (This can be especially annoying when you do upgrades.) It also matters because RDS only gives you 30 days of backups. Tons of businesses need more than that, and it's hard to achieve without using pg_dump. But for large databases, pg_dump can take hours and impact performance of other connections. On RDS you also have to deal with EBS expense and performance limitations. PIOPS are very expensive. Running on local disks is a lot faster. On plain EC2 you can do more to work around all that. Ephemeral storage gives you real disks, but they might not be big enough (and they don't scale independently of the instance size). A better approach is RAIDing over gp2 volumes. I've heard they did this at Reddit and Citus. I've set it up before and it has worked great. You might be able to find some details in my comment history. Go back a few years. . . . Of course if running in your own datacenter is an option, that's even simpler (in some dimensions anyway). To me the sweet spot is renting dedicated machines, e.g. from Wholesale Internet. RDS is sooo easy though. I have to admit it's hard not to recommend it for early-stage ventures. The limitations probably won't bite you until you're far along.
tylerjl 6y ago
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: https://crt.sh/?q=ycombinator.com https://crt.sh/?q=ycombinator.com 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.
- fiddlerwoaroof 6y ago
  I don’t understand the value of Certificate Transparency: I could see triggering an email to the owner of a domain every time a certificate is issued, but what’s the value of a public log? It seems to me that it just increases attack surface of your services.
  - strzibny 6y ago
    I agree, I would just do a self-signed cert.
101008 6y ago
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. 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?
- haolez 6y ago
  You can attach the Lambda functions to a VPC. So you can control their outbound IP.
stephen123 6y ago
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.
- alexk 6y ago
  Disclaimer: I work at Teleport If our Enterprise pricing is too high, take a look Teleport's open core version: https://github.com/gravitational/teleport https://github.com/gravitational/teleport
e2e4 6y ago
https://supabase.io/docs https://supabase.io/docs is a good example of adding a nice security later (and more) on top of postgres
SomaticPirate 6y ago
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. The application pods ingest the db credentials from Vault. The biggest concern we have today is automating credential rotation. Curious if anyone else has a similar setup or thoughts on ours?
- imglorp 6y ago
  Vault has a nice trick where they can create a temporary DB credential to hand to clients. If that cred is stolen, it's no good later. You have to convince Vault to give you a new cred if you want to talk to the DB. https://www.hashicorp.com/resources/securing-databases-with-dynamic-credentials-vault https://www.hashicorp.com/resources/securing-databases-with-...
nodamage 6y ago
> log_statement = all Is this something people really do? Wouldn't these logs be enormous and/or potentially leak unwanted information?
- atleta 6y ago
  Assuming the logs have the same visibility as the db files themselves, I don't think it makes a difference. It can affect performance, though. So we use it in development but not in production.
dathinab 6y ago
Is there any reason against using wireguard to prevent any external connections to postgres if both the postgres clients and server are under your full control (e.g. only your server connect to postgres)?
- chousuke 6y ago
  VPNs are commonly used to connect applications to databases living "somewhere else". I don't see any particular reason why wireguard would be a bad choice for that. It's certainly easier to configure than IPsec. Do still use SSL and password authentication too; a VPN alone isn't a complete solution.
traceroute66 6y ago
Seems the old adage of "beware of blog posts bearing gifts" strikes again. 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. 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. SQL Injection attacks are common as muck. Stored procedures are a quick and easy way to mitigate them. 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.
johnx123-up 6y ago
https://archive.ph/FsOHN https://archive.ph/FsOHN (as the article is now removed)

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 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. The advantages of this is that there is no "always-open" access to the instance. Not sure why the author does not advocate this.

polskibus 6y ago
How do you monitor node health?
- duckfang 6y ago
  You can put other machines with a highly restrictive set of network rules that allow data to cross inside the network and outside the network in very controlled ways. Email is one such way.
  - capableweb 6y ago
    > Email is one such way. I'm sorry but could you clarify? You mean that you monitor and/or collect data from hosts inside of a network via email somehow?
  - abhishekjha 6y ago
    I am curious. Most of the monitoring stacks include something like telegram/Prometheus to collect metrics and send to influxdb. How would you do this via email?
    - duckfang 6y ago
      Inside the he bastion network/VPC, there can be logging, monitoring, ad/kerb+openldap stuff to do the requisite thing. And your logging and monitoring can send trigger emails when they catch bad things. Your inbox needs to be an automatable inbox, say controlled by bleeder or another flowengine. From there you can build a full messaging dashboard and have it done in something that could pass a very stringent audit.
deleted 6y ago
[deleted]
duckfang 6y ago
Yep, and your answer is the better default answer. Bastion<->app host<->DB Bastion can talk to the net. App host can talk outbound but inbound only accepts bastion and DB. DB can only talk with app host. Obviously, you harden everything appropriately... But with this arrangement, it's very difficult to penetrate this sort of network. Think of it as a network that as a whole is default-deny.
chatmasta 6y ago
> Not sure why the author does not advocate this. Probably because the blog post is an advertisement for their product, which already allows you to implement bastion hosts as you describe. From the bottom: > Databases do not need to be exposed on the public Internet and can safely operate in air-gapped environments using Teleport’s built-in reverse tunnel subsystem.
- greatjack613 6y ago
  Did not catch that. Sneaky self promotion there :) Not sure what there reverse tunnel product is, but a bastion host is super easy to implement, just spin up an ec2 and walla. Curious as to what value they are providing
  - chatmasta 6y ago
    They've been around a while, it actually seems quite cool. Bastion hosts are simple to setup, sure, but Teleport adds a whole bunch of porcelain on top, e.g. integration with SSO, web UI for administration, etc. Haven't used them myself but I wouldn't be against trying it if in the market for something like that.
    - duckfang 6y ago
      I find that porcelain has a very bad time in breaking when you least suspect it. The same is true for server applications that have weird 3rd party dependencies that may go down when you least suspect it.
  - jdmichal 6y ago
    Unsure whether you meant walla, but just in case this is what you meant: It's "voila", from French "et violà". https://www.collinsdictionary.com/dictionary/french-english/et-voil%C3%A0 https://www.collinsdictionary.com/dictionary/french-english/...
zomgwat 6y ago
One alternative to keeping the bastion off is to restrict access to the bastion by IP address. Ideally, the list of IP addresses that need access to the bastion should be relatively small. In cloud environments, it's straightforward to update network firewall rules as IP addresses change. Residential and office IP addresses don't change much so it's not much of a hassle in my experience. That said, it can get annoying if you find your self working on a network that rotates your IP address frequently (e.g., a hotspot).
brightball 6y ago
Agreed. What would be the best way when using something like Heroku?
- fmajid 6y ago
  Something like Nebula (https://github.com/slackhq/nebula https://github.com/slackhq/nebula) or Tailscale, perhaps?
  - brightball 6y ago
    Can you setup Nebula on your Heroku dynos and an RDS instance?
120bits 6y ago
Currently I have a postgres/timescaledb running on EC2 in VPC which has no internet access. I do VPN tunnel to access private local subnet and have security group settings that allows my and 1 more machine access only. I usually have a jump server that I use, but I don't want to keep my ssh keys on the server or leave it behind.
- clan 6y ago
  If the jumpserver is trusted and controlled by you then you should have a look at ssh agent forwarding. Then you avoid leaving keys on the jumphost.
  - ylk 6y ago
    A better alternative would be ProxyJump. See e.g. https://serverfault.com/questions/958222/bastion-server-use-tcp-forwarding-vs-placing-private-key-on-server https://serverfault.com/questions/958222/bastion-server-use-... Edit: To add some details - using ProxyJump you don’t have to expose anything to the jump host and instead just proxy through it.
lovedswain 6y ago
What value is added by using a separate VPC? Equivalent restrictions can more easily be done with security groups, including on the outbound networking
- cj 6y ago
  I would suggest considering segregation by subnets (in addition to security groups), using public / private subnets, where any server in a private subnet (behind a nat gateway) doesn't/can't have a public ip address, and therefore cannot be accessed via the public internet. You could then use a bastion to access servers in the private subnet, or use something like AWS Session Manager which provides command line access via web browser in lieu of a bastion.
  - lovedswain 6y ago
    > What value is added by using a separate VPC? Adding more mechanisms on top is pointless when the effort could be invested in, for example, automated auditing of SGs, which is vastly more potent from a hardening perspective than adding additional layers of technical redundancy that are still exposed to the same flawed human processes. When you reach a team of 10-20 folk on a project, stuff tends to get confusing and/or lazy with elaborate configurations. Security design therefore is about more about managing that outcome through simplicity and process hardening than.. well.. I don't even know what threats a separate VPC protects against
    - cj 6y ago
      > When you reach a team of 10-20 folk on a project, stuff tends to get confusing From the perspective of maintaining security in the least confusing way possible in a team setting, I could see a scenario where you have a VPC that only has private subnets, and no public subnets at all. You could call it “Database VPC” and assuming your team doesn’t reconfigure the VPC to add public subnets, you can be comfortable with your team adding more EC2 servers / databases / whatever in that VPC since they would all be in private subnets inaccessible by the public internet. And then you could have a 2nd VPC with private/public subnets that are less locked down than the database VPC, which might contain your load balancers, application servers, etc. I suppose the benefit would be the logical separation of databases into a VPC without any public subnets. And the only way to gain access to subnets in that VPC would be through VPC peering. (The above assumes you’re using AWS). Although after typing all that, I still think you can accomplish a comparably secure (on a network level) architecture using security groups, or even separate subnets, without separate VPCs. In general I try to avoid multiple VPCs because VPC peering in AWS can get tricky (or impossible if both VPCs have overlapping CIDR blocks).
moltar 6y ago
I use almost the same approach. I also limit access to just one IP, which is a VPN server hosted on a different cloud provider. You can run a bulletproof VPN server easily using something like algo (https://github.com/trailofbits/algo https://github.com/trailofbits/algo)

Dowwie 6y ago

Check out using Hashicorp Vault for dynamic postgres credentials and securely obtaining them during system init

throwaway13337 6y ago

>By default, PostgreSQL listens on a TCP port 5432. This post seems to outright state that by default postgres is listening to everyone via TCP for connection. This is not true. 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. 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. 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.

deleted 6y ago
[deleted]
postgressomethi 6y ago
> Unless you edit pg_ident.conf, your postgres install will not listen for connections outside of on localhost. I don't know what the defaults are, but pg_ident.conf has absolutely nothing to do with this. The main configuration file (I think postgresql.conf usually) has listen_addresses, which controls the addresses on which postgres listens, as you might guess. pg_hba.conf (not pg_ident.conf) controls the authentication methods the server asks from the client, depending on how they're connecting.
- Quekid5 6y ago
  You're technically correct, but the difference is immaterial unless we're talking about outright pre-auth exploits. Or exploits of the auth itself, I guess. (I think I recall exactly one in the history of PostgreSQL since I started using it, but it is what it is.)
hankchinaski 6y ago
>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 Not anymore since version 3.2.0 [0] >Unfortunately many users fail to protect Redis instances from being accessed from external networks. Many instances are simply left exposed on the internet with public IPs. For this reasons since version 3.2.0, when Redis is executed with the default configuration (binding all the interfaces) and without any password in order to access it, it enters a special mode called protected mode. In this mode Redis only replies to queries from the loopback interfaces, and reply to other clients connecting from other addresses with an error, explaining what is happening and how to configure Redis properly. [0] https://redis.io/topics/security https://redis.io/topics/security

megous 6y ago

Removing the public schema and not allowing users to connect to all databases by default is also a good idea.

perlgeek 6y ago

What exactly is the security benefit of a reverse tunnel, in comparison to just listening on a port? Usually DB servers are "always on" and always accept connections, so the reverse tunnel also needs to be always up. 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. 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. Is there a way to drop from the service account into a less-privileged role inside a transaction or so?

old-gregg 6y ago
Disclaimer: I work at Teleport (but I am not the author of the article). This work was done because the Teleport users who used it for SSH kept asking for the same access for their databases. The reasoning goes like: 1. Setting up a single proxy gives you the same benefits for N databases as they come online. No need to manage additional endpoints (public IPs, ports, etc). 2. You have the same centralized place to manage auth/authz for all users. 3. This allows to connect to databases on the edge, where there isn't an opportunity to have a permanent public IP and locations frequently go online/offline. 4. Finally, it's nice to have unified visibility into what's available (for users) and centralized logging/audit for the security team. As always, all of this is possible with other tools. The world of open source is vast and full of options, but we were hoping to make it simpler, with less configuration and moving parts.
hobs 6y ago
I know in SQL Server a lot of products use the CONTEXT_INFO function for this, basically store some variable per connection and change it for context switching/row level security. Obviously you need to trust the service account enough to do that.

paulryanrogers 6y ago

> Unfortunately, this sort of air-gapped setup is not something PostgreSQL supports out-of-the-box. Is a reverse tunnel really air gapped? I thought AG meant one had to physically touch the device and transfer using devices without any network capability, such as a flash drive?

tyingq 6y ago
"Unfortunately, this sort of air-gapped setup is not something PostgreSQL supports out-of-the-box." Not sure what they mean by "out of the box", but you can make the listen_addresses list empty: "listen_addresses (string) ...If the list is empty, the server does not listen on any IP interface at all, in which case only Unix-domain sockets can be used to connect to it..." https://www.postgresql.org/docs/9.3/runtime-config-connection.html https://www.postgresql.org/docs/9.3/runtime-config-connectio...
- jagged-chisel 6y ago
  I’m sure the article author meant, by saying “out-of-the-box,” the features available on initial, default install. I disagree with the author and agree that indeed a fresh install supports (i.e. allows one to configure without rebuilding) not talking over a network at all.
  - tyingq 6y ago
    Ah, yes, re-read, and they do mention this.
gnabgib 6y ago
That's a good question. I agree with your definition of AG. However, the only time a database (or any other kind of data store) would be useful in an AG setting would be if it's part of an AG system including network and other computers. Perhaps we need another somewhat similar term. It's like null-routing or firewalling devices on your network.. they're technically "connected" but if they cannot dial out they're in some ways gapped. (This is handy for dubious quality IoT devices, they can't phone home, auto-patch to drop features, or share your usage information with $corp, but still respond to local network commands). To some extent tunneling is security through obscurity (an SSH tunnel has moved the port you need to secure from 5432 to 22)

fmajid 6y ago

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.

ed25519FUUU 6y ago
Stored procedures are really not used enough. They’re incredibly useful, and not just for security. People can make out-of-band updates to the query, like making it more efficient or migrating it, without requiring any changes to the all.
- doctor_eval 6y ago
  When used for business logic they also execute about 20x faster than the same logic encoded in a client, and in far fewer LOC. Getting rid of all those round-trips has a huge effect!
  - koolba 6y ago
    Running a slow hash operation that should take a non-negligible amount of CPU on your single core per process database server isn’t a great idea. That’s not a rag on stored procedures more generally. Just that specific use case scales poorly to a large number of operations as its inherently cpu bound.
    - tpetry 6y ago
      You can do the slow pbkdf2 hash on your webserver and use the result for your stored procedure to check the password. If the password column is only visible to the stored procedure you have used the best features of both systems.
      - 6y ago

indemnity 6y ago

Possibly stupid questions. (1) When is operating your own PostgreSQL instance desirable? (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?

vilified 6y ago
Some input on this would be greatly appreciated. For example: how much of this would I need not worry about if I use a managed postgres database (like from digitalocean or aws or any provider actually)
paulryanrogers 6y ago
1. When you don't want your data on other people's computers, or need extensions or configuration that's impractical, or need control over your uptime
edoceo 6y ago
I do it because of custom extensions we need. I don't think any cloud provider allow it.
mvolfik 6y ago
I'll be soon doing this for a project running on LAN without connection to internet, during events once COVID is over
strzibny 6y ago
1. Saving money 2. Need an extension that is not available 3. Running the whole app on a single node (to avoid network trips) 4. Want to learn
pjungwir 6y ago
Running your own Postgres is surprisingly easy, even with backups/monitoring/replication. Postgres replication has a reputation for being difficult to set up, but that's not what I've found. I think the reputation largely reflects how it was 15-20 years ago, not today. It's not completely turn-key I suppose, but there are many resources to learn. Speaking of monitoring: there are far fewer options if you're on RDS. Personally my favorite is munin, where you can instantly see all kinds of stats & history at multiple levels of abstraction. There are many excellent Postgres plugins to report on transactions, locking, etc., and it's easy to write your own. On RDS you can't install custom extensions. They have a whitelist of the most commonly-used ones, but if you find a different one (or build your own), you're out of luck. This really hampers you if you want to get the most from your database. I will say though, building custom extensions can also block you from using CI/CD solutions (or at least make them harder to set up), since they may have similar restrictions. Writing a custom extension is sort of a last restort, but it can be a huge boost for certain problems. RDS also doesn't grant you direct access to the WAL. That means you can't use WAL-E/WAL-G (a really nice incremental backup solution) or many other helpful tools. You can't do replication except via AWS's own black-box features. (This can be especially annoying when you do upgrades.) It also matters because RDS only gives you 30 days of backups. Tons of businesses need more than that, and it's hard to achieve without using pg_dump. But for large databases, pg_dump can take hours and impact performance of other connections. On RDS you also have to deal with EBS expense and performance limitations. PIOPS are very expensive. Running on local disks is a lot faster. On plain EC2 you can do more to work around all that. Ephemeral storage gives you real disks, but they might not be big enough (and they don't scale independently of the instance size). A better approach is RAIDing over gp2 volumes. I've heard they did this at Reddit and Citus. I've set it up before and it has worked great. You might be able to find some details in my comment history. Go back a few years. . . . Of course if running in your own datacenter is an option, that's even simpler (in some dimensions anyway). To me the sweet spot is renting dedicated machines, e.g. from Wholesale Internet. RDS is sooo easy though. I have to admit it's hard not to recommend it for early-stage ventures. The limitations probably won't bite you until you're far along.

tylerjl 6y ago

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: https://crt.sh/?q=ycombinator.com https://crt.sh/?q=ycombinator.com 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.

fiddlerwoaroof 6y ago
I don’t understand the value of Certificate Transparency: I could see triggering an email to the owner of a domain every time a certificate is issued, but what’s the value of a public log? It seems to me that it just increases attack surface of your services.
- strzibny 6y ago
  I agree, I would just do a self-signed cert.

101008 6y ago

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. 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?

haolez 6y ago
You can attach the Lambda functions to a VPC. So you can control their outbound IP.

stephen123 6y ago

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.

alexk 6y ago
Disclaimer: I work at Teleport If our Enterprise pricing is too high, take a look Teleport's open core version: https://github.com/gravitational/teleport https://github.com/gravitational/teleport

e2e4 6y ago

https://supabase.io/docs https://supabase.io/docs is a good example of adding a nice security later (and more) on top of postgres

SomaticPirate 6y ago

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. The application pods ingest the db credentials from Vault. The biggest concern we have today is automating credential rotation. Curious if anyone else has a similar setup or thoughts on ours?

imglorp 6y ago
Vault has a nice trick where they can create a temporary DB credential to hand to clients. If that cred is stolen, it's no good later. You have to convince Vault to give you a new cred if you want to talk to the DB. https://www.hashicorp.com/resources/securing-databases-with-dynamic-credentials-vault https://www.hashicorp.com/resources/securing-databases-with-...

nodamage 6y ago

> log_statement = all Is this something people really do? Wouldn't these logs be enormous and/or potentially leak unwanted information?

atleta 6y ago
Assuming the logs have the same visibility as the db files themselves, I don't think it makes a difference. It can affect performance, though. So we use it in development but not in production.

dathinab 6y ago

Is there any reason against using wireguard to prevent any external connections to postgres if both the postgres clients and server are under your full control (e.g. only your server connect to postgres)?

chousuke 6y ago
VPNs are commonly used to connect applications to databases living "somewhere else". I don't see any particular reason why wireguard would be a bad choice for that. It's certainly easier to configure than IPsec. Do still use SSL and password authentication too; a VPN alone isn't a complete solution.

traceroute66 6y ago

Seems the old adage of "beware of blog posts bearing gifts" strikes again. 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. 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. SQL Injection attacks are common as muck. Stored procedures are a quick and easy way to mitigate them. 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.

johnx123-up 6y ago

https://archive.ph/FsOHN https://archive.ph/FsOHN (as the article is now removed)