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

21–30 of 104 posts

Re: Securing a Postgres Database

#21

Post by old-gregg »

perlgeek wrote: post #19

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

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.

Re: Securing a Postgres Database

#22

Post by paulryanrogers »

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

Re: Securing a Postgres Database

#23

Post by ylk »

clan wrote: post #20
120bits wrote: post #14

Earlier quoted context omitted.

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.

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.

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.

Re: Securing a Postgres Database

#24

Post by lovedswain »

greatjack613 wrote: post #2

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

What value is added by using a separate VPC? Equivalent restrictions can more easily be done with security groups, including on the outbound networking

Re: Securing a Postgres Database

#25

Post by abhishekjha »

duckfang wrote: post #6
polskibus wrote: post #3

Earlier quoted context omitted.

How do you monitor node health?

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.

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?

Re: Securing a Postgres Database

#26

Post by fmajid »

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.

Re: Securing a Postgres Database

#27

Post by postgressomethi »

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 authentication methods the server asks from the client, depending on how they're connecting.

Re: Securing a Postgres Database

#28

Post by ed25519FUUU »

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…

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.

Re: Securing a Postgres Database

#29

Post by fmajid »

brightball wrote: post #12
greatjack613 wrote: post #2

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

Agreed. What would be the best way when using something like Heroku?

Something like Nebula (https://github.com/slackhq/nebula) or Tailscale, perhaps?

Re: Securing a Postgres Database

#30

Post by cj »

lovedswain wrote: post #24
greatjack613 wrote: post #2

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

What value is added by using a separate VPC? Equivalent restrictions can more easily be done with security groups, including on the outbound networking

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.

Post reply on HN