Calebotomy

PostgreSQL initial setup (authentication)

If you followed my last initial setup post on creating users and are running a default install of postgres you currently should be able to access the database on your cli. However, many distributions do not install postgres in a default manner. A great many distributions make the default authentication for sockets ident sameuser. This is technically much more secure than trust which is the default. However, you may find yourself locked out of your database on the local cli if you make the database name different. I personally was confused about this after first encountering this setting.

So, we don’t want trust but we want to use a db username other than our shell login, most likely because we have more than one database. There are several options but I personally think ident is a good one. However, since we don’t want to use the same shell login name we have to modify pg_ident.conf and pg_hba.conf, locations vary by distribution.

In pg_ident.conf you have to create a line with the following formatting.

# MAPNAME IDENT-USERNAME PG-USERNAME

I think it’s mostly straightforward. In case it isn’t, MAPNAME is an arbitrary identifier, sameuser is actually the mapname in ident sameuser A quick example from mine would be

devel xenoterracide webdevwhere my unix username is xenoterracide but I created the database user webdev. If you wanted you could add another devel mapname with another user or the same unix account different db account, or even a different unix account same db account, etc.

After you add all the various mappings you need to add or change the ident in pg_hba.conf. You can only have one method per type/database/user/address combination. so in pg_hba.conf you want to change

local all all to local all all ident devel

If you want postgres to ask for a password use md5 instead of ident further information can be found at http://www.postgresql.org/docs/current/interactive/auth-methods.html.

if you have any problems you might want to see part 2.


Share

comments powered by Disqus