Postgresql pg_hda.conf 환경설정.

요즘 미친듯이 바뻐서 정말로 오랜만(?) 거의 한달 만에 포스팅 하는것 같아요.

정말 정말 미친듯이 바쁘게 하루 하루 보내고 있습니다. 하루하루가 고달프면서

잠을 청할때는 ㅡㅜ 이 바닥 언제 떠나나 하면서 깜빡하면 해는 뜨고... 하루하루

의식이 몽롱해지네요.. 쫌... 정신좀 차려야 하는데...

this file is usually located in $PGDATA directory, but not always (hi, “genius, let’s do it differently” debian guys). if you’re using modern postgresql you can check where the file is located:

# show hba_file ;
hba_file
------------------------------
/home/pgdba/data/pg_hba.conf
(1 row)

or set it explicitly in postgresql.conf.

this file is the first line of defense against hackers. it tells postgresql from which ip numbers, which users to which databases can connect and how they can connect.

to understand this config file you have to understand couple of facts:

• postgresql “listens” for connections using unix sockets (located in /tmp or another place – you can check using: netstat -nxl | grep PGSQL)
• postgresql can listen for connections on tcp/ip port (check: show listen_addresses; show port;)

now, in pg_hba.conf there are rules which allow or prevent logging using unix/tcpip sockets, and various combinations of ip/database/username.

let’s see an example:

# TYPE DATABASE USER CIDR-ADDRESS METHOD
local all pgdba md5
local all all trust
host all pgdba 0.0.0.0/0 reject
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
host all all 192.168.1.69/32 trust
host all all 192.168.1.0/24 md5
host all all 0.0.0.0/0 reject

that’s a lot of lines, but what they all mean?

first, you have to understand, that postgresql stops “processing” the file when it finds first line that matches given connection.

so, in our example when i will try to login as “pgdba” to database “xxx” using tcp/ip socket, connecting from 192.168.1.222 – used line will be “host all pgdba 0.0.0.0/0 reject”, and not “host all all 192.168.1.0/24 md5″ (which matches more specific ip range) – simply because it’s first.

so, now to the meaning of pg_hba.conf lines:

local all pgdba md5

local means it is for unix-socket connections only. all – means any database. pgdba – means that this rule will be applied when logging using pgdba account (postgresql pgdba account, not shell account!). md5 at the end means that the authentication method to be used is md5.

basically there are only few authentication methods:

• trust
• password
• md5
• ident
• reject

there are also some others, but definitely less used (if you’re curious: gss, sspi, kdb5, pam, ldap).

meaning of them is quite simple:

• trust – no checks against password are made. postgresql trusts that you are then one that you’re saying you are.
• password/md5 – to connect you have to supply password for this account (again, postgresql account). password method is generally less safe, and shouldn’t be use unless you tested that md5 doesn’t work. remember that the method (md5/password) has nothing to do with password encoding in database.
• ident – i will describe it later. for now simply assume it’s devil incarnate, root of all evil
• reject – simple authentication method which rejects any kind of connections

so, now that we know this, we can read the rest of example pg_hba.conf file:

local all all trust

this means that any user connecting to any database, but using unix socket, is trusted. remember though about the first rule which was for pgdba user. knowing about how postgresql read the file, we know that this rule effectively means: all users except pgdba, which was mentioned in previous line.

now, this “trust” can be seen as bad, but consider this: in standard situation one using unix socket to connect to pg, has to have the ability to run the process on your machine. so knowing this – it’s not much of a problem to give him “trust” when it comes to postgresql connection. (i said standard because dblink/dbilink modules bring another factor to our security issue, but they are definitely not the most used extensions, and i assume anyone using them already knows what i’m writing in here).

host all pgdba 0.0.0.0/0 reject

this line effectively forbids any logging to pgdba over tcpip sockets. doesn’t matter which ip you are using. if you are using tcpip (thus the “host” at the beginning of the line) – you can’t connect as pgdba.

host all all 127.0.0.1/32 trust
host all all ::1/128 trust

these 2 lines allow trusted connections from localhost – even using tcp/ip sockets. of course – to all users except pgdba, which was excluded by previous line.

host all all 192.168.1.69/32 trust

this line shows that user using computer with ip 192.168.1.69 has special rights and can login using “trust” to any database and using any username (except pgdba of course).

host all all 192.168.1.0/24 md5

this line means that any user from 192.168.1.0/24 network (with the exception from previous line) can login to any account (minus pgdba) using md5 authentication.

host all all 0.0.0.0/0 reject

the last line forbids any other logins from any other hosts. actually it is not needed as postgresql will automatically reject connection when there is no line for it in pg_hba.conf.

that’s all. simple, and very effective.

to give some perspective i usually use pg_hba with this content:

local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
host all all 0.0.0.0/0 md5

this means that from localhost i can login (using both unix socket and tcpip socket) to any database and any user without giving password. and when i want to connect from another machine – any other machine – i still can connect to any user, but i have to know the password.

what else can be done using pg_hba.conf? for example this:

we have database “xxx” which we allow only one user to connect to: pornadmin. to conenct using pornadmin to xxx database you have to supply password. no other user should be allowed to connect to this database, but when portadmin will want to connect to another database – password will not be needed.

how to do it? it’s simple:

host xxx pornadmin 0.0.0.0/0 md5
host xxx all 0.0.0.0/0 reject
host all all 0.0.0.0/0 trust

that’s all.

so, this file is very simple and very powerful. but what about the title problem? failed ident authen?

ident authen is very good idea. basically it allows administrator to setup rights in such a way that given *shell* user will be able to connect only using postgresql user that has the same name as shell user.

for example – setting:

local all all ident sameuser

i enforce that shell user “depesz” can connect to database *only* as user depesz.

this has some uses. for for majority of users which just installed postgresql (probably going straight from mysql) it is a major pita.

this shouldn’t be a problem. postgresql developers left default pg_hba.conf in a state where is “trust” local connections, and rejects any other.

but the “great” debian guys thought that it’s bad. and they package postgresql with modified default pg_hba.conf file which sets “ident sameuser” for all local connections.

effects?

default superuser in postgresql is postgres. to connect to this account i cannot use “depesz” shell account as it is not “postgres”. so i have to “su” to user postgres. but user postgres doesn’t have password (usually). so i have first to “su” to root, then to postgres, and then i can connect to postgresql database as postgres user. for example to create new user which will be useful for me.

now. i know that ident has its uses. yet i truly believe that leaving it as default for standard postgresql installation does a lot of harm.

why? it’s simple. theoretically it’s more secure. but:

• if i want security, it means i’m security-conscious admin, and i most probably read the docs, and know how to set it myself
• newbie users have a major problem (which is visible on irc and mailing lists) connecting to postgresql. which looks like if some pro-mysql guy thought about it as a way to scare people off postgresql.
• security is an illusion, when for the simplest tasks (like create new user in postgresql database, create new database) you have to go through your root account!

so, ending this post – if you’ll ever see this error (FATAL: Ident authentication failed), please find your pg_hba.conf file, change the setting to sane, and (optionally) write your opinion to postgresql-package maintainers.