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

31–40 of 104 posts

Re: Securing a Postgres Database

#31

Post by hobs »

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…

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.

Re: Securing a Postgres Database

#32

Post by indemnity »

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?

Re: Securing a Postgres Database

#33

Post by doctor_eval »

ed25519FUUU wrote: post #28
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.

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!

Re: Securing a Postgres Database

#34

Post by cooperaustinj »

ed25519FUUU wrote: post #28
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.

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.

Re: Securing a Postgres Database

#35

Post by lovedswain »

cj wrote: post #30
lovedswain wrote: post #24

Earlier quoted context omitted.

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

Re: Securing a Postgres Database

#36

Post by koolba »

doctor_eval wrote: post #33
ed25519FUUU wrote: post #28

Earlier quoted context omitted.

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.

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!

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.

Re: Securing a Postgres Database

#37

Post by gnabgib »

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?

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)

Re: Securing a Postgres Database

#38

Post by vilified »

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?

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)

Re: Securing a Postgres Database

#39

Post by paulryanrogers »

gnabgib wrote: post #37
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?

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

Re: Securing a Postgres Database

#40

Post by [deleted] »

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?

[deleted]
Post reply on HN