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

81–90 of 104 posts

Re: Securing a Postgres Database

#81

Post by mnahkies »

dijit wrote: post #79
WaxProlix wrote: post #77

Earlier quoted context omitted.

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.

Hmm I think the default pg_hba.conf uses "trust" and so will allow passwordless connections as postgres on localhost, you just have to specify the username (psql will use your username by default)

This is the case for the official docker image at least, and I'm fairly certain also true of distro package managers

Re: Securing a Postgres Database

#82

Post by moltar »

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

I use almost the same approach.

I also limit access to just one IP, which is a VPN server hosted on a different cloud provider.

You can run a bulletproof VPN server easily using something like algo (https://github.com/trailofbits/algo)

Re: Securing a Postgres Database

#83

Post by dathinab »

Is there any reason against using wireguard to prevent any external connections to postgres if both the postgres clients and server are under your full control (e.g. only your server connect to postgres)?

Re: Securing a Postgres Database

#84

Post by chousuke »

dathinab wrote: post #83

Is there any reason against using wireguard to prevent any external connections to postgres if both the postgres clients and server are under your full control (e.g. only your server connect to postgres)?

VPNs are commonly used to connect applications to databases living "somewhere else". I don't see any particular reason why wireguard would be a bad choice for that. It's certainly easier to configure than IPsec.

Do still use SSL and password authentication too; a VPN alone isn't a complete solution.

Re: Securing a Postgres Database

#85

Post by [deleted] »

mnahkies wrote: post #81
dijit wrote: post #79

Earlier quoted context omitted.

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.

Hmm I think the default pg_hba.conf uses "trust" and so will allow passwordless connections as postgres on localhost, you just have to specify the username (psql will use your username by default) This is the case for the official docker image at least, and I'm fairly certain also true of distro package managers

[deleted]

Re: Securing a Postgres Database

#86

Post by defanor »

chasd00 wrote: post #70
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?

Air gap means there’s no physical connection. Like literal air between one wire and the other. Air gap should never be thought of something like a “logical air gap” because so such thing exists. Either the cable is physically plugged in or it isn’t.

Pretty sure that the common definition (also the one from Wikipedia [0]) is that there's no connection to a network (including wireless ones; the medium is not an important part in the concept).

The article appears to say that a database/DBMS in the ideal world is not accessible over network at all. That is, apparently only accessible by users who have physical access to the machine it is on.

[0] https://en.wikipedia.org/wiki/Air_gap_(networking)

Re: Securing a Postgres Database

#87

Post by boromisp »

mnahkies wrote: post #81
dijit wrote: post #79

Earlier quoted context omitted.

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.

Hmm I think the default pg_hba.conf uses "trust" and so will allow passwordless connections as postgres on localhost, you just have to specify the username (psql will use your username by default) This is the case for the official docker image at least, and I'm fairly certain also true of distro package managers

At least on debian / ubuntu, the default is peer auth: https://salsa.debian.org/postgresql/postgresql-common/-/blob...

Re: Securing a Postgres Database

#88

Post by koolba »

tpetry wrote: post #78
koolba wrote: post #36

Earlier quoted context omitted.

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.

No you can’t. To do the hash you need the per user salt before you start hashing. Which would require reading from the DB.

Re: Securing a Postgres Database

#89

Post by hannihanni »

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…

Lock down port-level access to the PostgreSQL database is important though

Re: Securing a Postgres Database

#90

Post by mvolfik »

koolba wrote: post #88
tpetry wrote: post #78

Earlier quoted context omitted.

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.

No you can’t. To do the hash you need the per user salt before you start hashing. Which would require reading from the DB.

Then you can retrieve salt, do hash on server, and use the stored procedure to compare. More requests, but a tradeoff for security \o/
Post reply on HN