Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Securing a Postgres Database

goteleport.com

Post reply on HN
Print view View original

41–50 of 104 posts

Re: Securing a Postgres Database

#41

Post by tylerjl »

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.

Re: Securing a Postgres Database

#42

Post by tmpz22 »

paulryanrogers wrote: post #39
gnabgib wrote: post #37

Earlier quoted context omitted.

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…

Air gapped data stores sound useful for very common scenarios like: offline backups, non-anonymized PII, infrequently accessed bank records

I.E. for long term archiving or legal compliance.

Re: Securing a Postgres Database

#43

Post by paulryanrogers »

indemnity wrote: post #32

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?

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

Re: Securing a Postgres Database

#44

Post by edoceo »

indemnity wrote: post #32

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?

I do it because of custom extensions we need. I don't think any cloud provider allow it.

Re: Securing a Postgres Database

#45

Post by hankchinaski »

throwaway13337 wrote: post #13

>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…

>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

Re: Securing a Postgres Database

#46

Post by tyingq »

paulryanrogers wrote: post #22

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

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

Re: Securing a Postgres Database

#47

Post by throwawayboise »

fmajid wrote: post #26

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 abl…

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.

Re: Securing a Postgres Database

#48

Post by jagged-chisel »

tyingq wrote: post #46
paulryanrogers wrote: post #22

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

"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-co…

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.

Re: Securing a Postgres Database

#49

Post by cj »

lovedswain wrote: post #35
cj wrote: post #30

Earlier quoted context omitted.

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 acces…

> 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…

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

Re: Securing a Postgres Database

#50

Post by Quekid5 »

postgressomethi wrote: post #27
throwaway13337 wrote: post #13

>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…

> 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 authentic…

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

Post reply on HN