Setting up Postgresql - security?

dougal85

I've been a mySQL user for years but recently giving Postgresql a shot. I just installed it on a CentOS 5.2 box.

I followed this tutorial; http://www.flmnh.ufl.edu/linux/install_postgresql.htm

/var/lib/pgsql/data/pg_hba.conf

Modify the local line to use "trust" based authentication rather than "identity". Please review the PostgreSQL documentation before making this change and take the security
local all all trust

In that section of the tutorial is suggests I check with the manual before making that change (I made it anyway because its just a dev box). However, can somebody point me to the right part in the manual or explain what it means? I was a bit confused by it 🙂

Sxooter

This is one of those chicken / egg parts of the manual. do you explain how accounts work and THEN how the authentication works, or vice versa?

Anyway, the part of the server you're editing, pg_hba.conf stands for "host based authentication" and it allows you to setup various security for each type of connection coming into the server. the file is covered here:

http://www.postgresql.org/docs/8.3/interactive/client-authentication.html

specifically under the pg_hba.conf section, but that whole section explains what's going on. In short, setting pg_hba.conf to trust says "let whoever log in as whoever they want to to any database they want to." Handy for testing etc... Not so good for a multiuser environment where you don't / can't trust everybody.

dougal85

Thankts that helped lots. I just found this (may be useful for others);

http://developer.postgresql.org/pgdocs/postgres/auth-pg-hba-conf.html

Sxooter

Just keep in mind that's the page for pg_hba.conf for 8.4. While it's not likely a lot will change from 8.3 to 8.4, there might be new features or changes that mean it won't work exactly the same in 8.3 or before.

This page:

http://www.postgresql.org/docs/8.3/interactive/auth-pg-hba-conf.html

is the version for 8.3, and there's one for 8.2 and so on. Some things have changed over time as some features have been added.

Two of my favorite settings for intranets are sameuser and samerole (samerole replaces samegroup, but samegroup still works in 8.3 and 8.4 so far)). Note that nothing for 8.4 is set in stone, so if someone decided to prune out the samegroup functionality at the last minute it would change.

BTW, I'm looking forward to running 8.4 a bit. Every new version of pgsql seems to include so much cool stuff.