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_hba.conf, locations vary by distribution.
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.