Securing a Postgres Database

Post by tyingq » Fri, Apr 02, 2021, 10:37 PM UTC

jagged-chisel wrote: post #48

tyingq wrote: post #46

Earlier quoted context omitted.

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

Post by vbezhenar » Fri, Apr 02, 2021, 10:43 PM UTC

throwawayboise wrote: post #47

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.

Post by haolez » Fri, Apr 02, 2021, 10:52 PM UTC

101008 wrote: post #54

This is surely a silly question (and probably a lot of devops will think I am an idiot), but what do you recommend to secure a PostgreSQL that is accesed by different IPs? We use Azure PostgreSQL database, which I guess they take care of most of the security, but they allow us to set up firewall rules, as only allowing connectins by established IPs. My (our) problem is that we use a lof ot AWS lambda functions that r…

Post by e2e4 » Fri, Apr 02, 2021, 10:58 PM UTC

Post by citrin_ru » Fri, Apr 02, 2021, 11:17 PM UTC

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.