Create user and database in Postgres

Follow me for more content or contact for work opportunities:
Twitter / LinkedIn

While I love Postgres, I get some problems every time I want to do the simple operation of creating a database with an associated user if it's been a while since the last time I did it.

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
  3. Create the database $ sudo -u postgres createdb -O
  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 ""

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 -W

Follow me for more content or contact for work opportunities:
Twitter / LinkedIn