Creating a database and user for local postgres development

So, yeah. I'm a postgres n00b. But I'm a n00b who wants to be able to create a non-superuser account and database, relate the two and also be able to remember how to do this again in two weeks time.

Connecting

As superuser via UNIX user authentication:
sudo -u postgres psql postgres

As superuser directly (-W forces password prompt):
psql -U postgres -W

As a user to a specific database:
psql database -U username -W

Basic commands

Show tables:
\d

List users:
\du

List databases:
\l

Quit:
\q

User management

CREATE USER username WITH PASSWORD 'password';
DROP USER username;

Database management

CREATE DATABASE database;
DROP DATABASE database;


Granting ownership and permissions

ALTER DATABASE database OWNER TO username;
GRANT ALL PRIVILEGES ON DATABASE database TO username;
Dumb settings for local dev.

Also, if you're getting problems connecting try replacing local peer with local md5 in /etc/postgresql/VERSION/main/pg_hba.conf.

Much of this came from this post. I'm planning on using pgAdmin3 as a database explorer when I want something quicker than the command line (on Ubuntu).