A short memo for configuring postgresql to use password (md5) authentication.

by thk — last modified 2007-03-06 02:18

settings for postgresql 8.3 are at folder

/etc/postgresql/8.3/main/

1. modify pg_hba.conf to enter as postgres user in the database without password (this is the default so probably you won't have to change anything)

local   all        ​postgres       ​        ​        ​  trust 

reload service

sudo /etc/init.d/postgresql-8.3 reload

2. then su to user postgres (you have to be root) and login to  postgresql

su postgres
psql -U postgres -d template1
3. change postgres user  password:

ALTER USER postgres with encrypted password 'pass';--add a user like thisCREATE USER user WITH PASSWORD 'pass';--grant permission to creat dbALTER USER username CREATEDB;




4. change again pg_hba.conf

set up md5 autentication for postgres and all other users

local   all        ​postgres       ​        ​        ​  md5 

local   all        ​all       ​        ​        ​  md5 

5. reload service.

sudo /etc/init.d/postgresql-8.3 reload

==================================================​=

show tables

SELECT table_name FROM information_schema.tables ;

SHOW DATABASES (l) = SELECT datname FROM pg_database;

SHOW COLUMNS FROM table (d table) = SELECT column_name FROM information_schema.columns WHERE table_name ='table';

== Open tcp Connections =============

1. at pg_hba.conf

 IPv4 local connections:
host    all        ​all         192.168.1.0/24      ​        password

# IPv6 local connections:
host    all        ​all         192.168.1.0/24      ​        password

-- this open postgres to your local lan only (modify accordingly..)

maybe some other athentication method -more secure-  may be used
if you want to operate in network mode permanently and not temporarily.

2. postgresql server settings

a) On Suse 10.1 at /etc/sysconfig/postgresql

POSTGRES_OPTIONS="-i"

b) On Ubuntu Server 6.06 at /etc/postgresql/8.1/main/postgresql.conf

listen_addresses = '192.168.1.33'

if you want to listen only to your local lan (modify to your local lan ip address) ,
 '*'  if you want  to  listen at all network interfaces.

CREATE USER

create role user_name with password 'pass' createdb createrole login;

backup

pg_dump -Ft dbname -U username -f backup.tar

restore

pg_restore -d dbname -U username -v backup.tar