There are several posts on the Internet about Postgres authentication, but I couldn't find any explaining exactly what I wanted to know, so here is mine.
This has been tested on Debian 6 and PostgreSQL 8.4.
1. Install the PostgreSQL server (obvious)
2. Create the user:
$ sudo -u postgres createuser -D -A -P <my-user>
3. Create the database
$ sudo -u postgres createdb -O <my-user> <my-database>
4. Edit /etc/postgresql/8.4/main/pg_hba.conf
# Put your actual configuration here
local all all password
host all all 127.0.0.1/32 password
NOTE: Make sure that your settings are placed after the comment saying where your configurations go. If you place them at the end, the default ones will be used, and you'll see this error when logging in:
psql: FATAL: Ident authentication failed for user "<my-user>"
Actually, you'll probably want to customize the settings you want to use. My settings allow logging in from localhost using unencrypted password, but may be you want to access from another host, only grant access to some users or some databases, or use another authentication methods, so I would recommend you reading the pg_hda.conf reference.
Finally, you'll be able to access by:
$ psql -U <my-user> -W
Another way to do it:
ReplyDelete1. As any superuser (postgres, pgsql, it depends on your OS):
psql -U superuser postgres
(superuser is your superuser username)
2. Then in the psql shell:
create USERNAME with createdb encrypted password 'SECRETPASSWORD';
3. Then logout from the psql shell (ctrl+d) and execute:
createdb -U USERNAME DATABASE_NAME
This approach would be better if you need the user to be able to create new databases.