Prettymad.net

Just a few postgres tips

Add a new User

Checkout the official docs

Login as the postgres user 

omni:~# su - postgres
postgres@omni:~$

Log into psql using template1 database, create the user with createdb authority 

postgres@omni:~$ psql template1
template1=# create user myusername with createdb password 'mypassword';
CREATE USER

Enable listening on TCP/IP

omni:~# vi /etc/postgresql/8.0/main/postgresql.conf

listen_addresses = '*'  # what IP interface(s) to listen on;

Add permissions to pg_hba.conf

Now we need to let permissions for this user to login via the pg_hba.conf file 

omni:~# vi /etc/postgresql/8.0/main/pg_hba.conf

# Add the line, which lets 'myusername' connect via linux sockets
local   all         myusername                          md5

# Add the following, which lets the 'myusername' connect via tcp
host    all         myusername    127.0.0.1/32          md5
host    all         myusername    172.16.77.0/24        md5

Create database with new user as owner

Log in as this user, and create a new database using this user 

omni:~# psql -U myusername -W
Password: secret
template1=> create database mydatabase owner=myusername;
CREATE DATABASE