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

71–80 of 104 posts

Re: Securing a Postgres Database

#71

Post by fiddlerwoaroof »

tylerjl wrote: post #41

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

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.

Re: Securing a Postgres Database

#72

Post by asguy »

rascul wrote: post #61
tinus_hn wrote: post #52

Earlier quoted context omitted.

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?

> What is the advantage of listening on localhost compared to using a socket, with free access control? Can install and use right away locally without figuring out where your distro puts the socket at. Edit: Also no need to play with permissions of the socket in such a case.

Unless your local cluster doesn’t allocate your install on 5432 e.g. after cluster upgrade.

Re: Securing a Postgres Database

#73

Post by nirui »

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…

I saw nobody mentioning Docker here, so just a reminder: If you do `-p 5432:5432`, there is no different than listening `0.0.0.0`.

Re: Securing a Postgres Database

#74

Post by c2h5oh »

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…

I've checked postgres repository and default has been to listen on localhost for at least 15 years.

https://github.com/postgres/postgres/blob/master/src/backend...

Re: Securing a Postgres Database

#75

Post by fulafel »

juotlrjrb wrote: post #63
tinus_hn wrote: post #52

Earlier quoted context omitted.

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?

Modern linux security thinking is that any sort of code running inevitably leads to root privilege. Put another way, any user can become root through privilege escalation, so access control is pointless, since any untrusted user can take over the machine. The real unit of security is the whole OS (VM), not its internal user boundaries.

This kind of mindset exists but is not a consensus. And even if it became consensus, it would still take many, many years for most developers to stop using ssh forwarding with localhost listeners in a security reliant way.

Also, the loopback is used as a networking interconnect or guest->host channel for sandboxed containers and VMs, so it's security sensitive in this way.

Re: Securing a Postgres Database

#76

Post by fmajid »

citrin_ru wrote: post #59
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.

I have an impression that many developers actively avoid stored procedures, trigger or any 'complex' database features because they don't fit well into a typical CI/CD pipeline which deals only with application code. It is quite possible to test stored procedures, it's just requires some additional work/infrastructure.

It's mostly a familiarity and education problem, as well as the fact MySQL didn't have good support for this. Developers are exposed to procedural programming languages at university, but seldom to databases, let alone stored procedures. Unit tests can be written in SQL or PL/PGSQL and will typically run much faster than a CI pipeline.

Re: Securing a Postgres Database

#77

Post by WaxProlix »

c2h5oh wrote: post #74
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…

I've checked postgres repository and default has been to listen on localhost for at least 15 years. https://github.com/postgres/postgres/blob/master/src/backend...

To be super clear, because it can be less than perfectly obvious, this means on `localhost` but also on loopback. There is no 'real' network device talking to this port.

Re: Securing a Postgres Database

#78

Post by tpetry »

koolba wrote: post #36
doctor_eval wrote: post #33

Earlier quoted context omitted.

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.

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.

Re: Securing a Postgres Database

#79

Post by dijit »

WaxProlix wrote: post #77
c2h5oh wrote: post #74

Earlier quoted context omitted.

I've checked postgres repository and default has been to listen on localhost for at least 15 years. https://github.com/postgres/postgres/blob/master/src/backend...

To be super clear, because it can be less than perfectly obvious, this means on `localhost` but also on loopback. There is no 'real' network device talking to this port.

it also isn't "open"; your username has to match the postgres database username to connect.

IE; the 'postgres' unix user is required to access databases as the 'postgres' database user.

Re: Securing a Postgres Database

#80

Post by jhardy54 »

nirui wrote: post #73
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…

I saw nobody mentioning Docker here, so just a reminder: If you do `-p 5432:5432`, there is no different than listening `0.0.0.0`.

Right, which is why it’s absolutely critical to bind to 127.0.0.1 instead of 0.0.0.0 in your docker / docker-compose config.
Post reply on HN