[HN Gopher] Securing a Postgres Database
___________________________________________________________________
Securing a Postgres Database
Author : bryanmikaelian
Score : 130 points
Date : 2021-04-02 19:02 UTC (3 hours ago)
(HTM) web link (goteleport.com)
(TXT) w3m dump (goteleport.com)
| indemnity wrote:
| 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?
| paulryanrogers wrote:
| 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 wrote:
| I do it because of custom extensions we need. I don't think any
| cloud provider allow it.
| vilified wrote:
| 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)
| 101008 wrote:
| 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 wrote:
| You can attach the Lambda functions to a VPC. So you can
| control their outbound IP.
| stephen123 wrote:
| 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.
| e2e4 wrote:
| https://supabase.io/docs is a good example of adding a nice
| security later (and more) on top of postgres
| SomaticPirate wrote:
| 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?
| megous wrote:
| Removing the public schema and not allowing users to connect to
| all databases by default is also a good idea.
| Dowwie wrote:
| Check out using Hashicorp Vault for dynamic postgres credentials
| and securely obtaining them during system init
| paulryanrogers wrote:
| > 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 wrote:
| _" 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-connectio...
| jagged-chisel wrote:
| 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 wrote:
| Ah, yes, re-read, and they do mention this.
| gnabgib wrote:
| 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)
| paulryanrogers wrote:
| Air gapped data stores sound useful for very common scenarios
| like: offline backups, non-anonymized PII, infrequently
| accessed bank records
| tmpz22 wrote:
| I.E. for long term archiving or legal compliance.
| [deleted]
| fmajid wrote:
| 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.
| throwawayboise wrote:
| I mean, it's likely you have some other stored procedure that
| _does_ enumerate users, for use by app adminstrators. Ideally
| it doesn 't return password hashes though, since they are
| really not useful for humans to look at. Also ideally, the
| admin stored procedures are not executable by the end-user app.
| vbezhenar wrote:
| Usually application uses connection pool to connect with
| database server. I'm not sure if that pattern could be used,
| when different requests need different database roles.
| ed25519FUUU wrote:
| 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 wrote:
| 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 wrote:
| 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.
| cooperaustinj wrote:
| I agree with the password example. I don't agree with people
| updating queries willy-nilly. Updated queries should go
| through the same life-cycle of testing (etc) as code, meaning
| they can simply go out with continuous application
| deployments anyways.
| greatjack613 wrote:
| 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 wrote:
| How do you monitor node health?
| duckfang wrote:
| 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.
| abhishekjha wrote:
| 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?
| capableweb wrote:
| > 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?
| [deleted]
| duckfang wrote:
| 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.
| lovedswain wrote:
| 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 wrote:
| 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 wrote:
| > 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 wrote:
| > 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).
| chatmasta wrote:
| > 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 wrote:
| 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 wrote:
| 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 wrote:
| 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.
| brightball wrote:
| Agreed. What would be the best way when using something like
| Heroku?
| fmajid wrote:
| Something like Nebula (https://github.com/slackhq/nebula) or
| Tailscale, perhaps?
| 120bits wrote:
| 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 wrote:
| 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 wrote:
| A better alternative would be ProxyJump. See e.g.
| 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.
| zomgwat wrote:
| 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).
| throwaway13337 wrote:
| >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.
| hankchinaski wrote:
| >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
| [deleted]
| postgressomethi wrote:
| > 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 wrote:
| 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.)
| tinus_hn wrote:
| The worst of both worlds: not accessible from the outside, yet
| insecure because it's accessible to any user on the inside.
|
| What is the advantage of listening on localhost compared to
| using a socket, with free access control?
| perlgeek wrote:
| 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 wrote:
| 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 wrote:
| 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.
| tylerjl wrote:
| 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
|
| 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.
___________________________________________________________________
(page generated 2021-04-02 23:00 UTC)