PostgreSQL

Installation and basic usage of PostgreSQL 9.X

Tested versions:

• Fedora 17 x86_64
• PostgreSQL 9.1.4 x86_64

A. Install packages:

#---

yum -y install \
postgresql \
postgresql-server

#---

B. Initialize environment:

#---

su - postgres

#---

#---

pwd

#----

Expected:

/var/lib/pgsql

#---

/usr/bin/initdb /var/lib/pgsql

logout

#---

C. Put PostgreSQL service to run

1. Check if PostgreSQL is running:

#---

systemctl status postgresql.service

#---

1.1. If not, just enable it and start it:

#---

systemctl enable postgresql.service

systemctl start postgresql.service

#---

1.2. Check if it has listeners running:

#---

netstat -nl --inet --inet6 | grep 5432

#---

Expected:

tcp        0      0 127.0.0.1:5432              0.0.0.0:*                   LISTEN     

tcp        0      0 ::1:5432                    :::*                        LISTEN  

D. Check accessibility

#---

psql -U postgres -c "SELECT 1;"

#---

Expected:
 ?column?

----------

        1

(1 row)

E. Set postgres user password

#---

psql -U postgres -c "ALTER USER postgres WITH PASSWORD 'postgres';"

#---

F. Close 'trust' access

#---

su - postgres

#---

1. Edit [/var/lib/pgsql/data/pg_hba.conf]

Change all 'trust' entries by 'password'

Example:

local   all   postgres   trust

Should become:

local   all   postgres   password

2. Restart PostgreSQL

#---

systemctl restart postgresql.service

#---

3. Check access:

#---

psql -U postgres -c "SELECT 1;"

#---

Expected:

Password for user postgres:

 ?column?

----------

        1

(1 row)

G. Database and users 101:

1. Create user:

#---

createuser -U postgres -W -D -R -S test_user

psql -U postgres -W -c "ALTER USER test_user WITH PASSWORD 'test_pass';"

#---

2. Create database:

#---

createdb -U postgres -W -E UTF8 -O test_user test_db

psql -U postgres -W -c "GRANT ALL ON DATABASE test_db TO test_user;"

#---

3. Check access:

#---

psql -U test_user -d test_db -W -c "SELECT 1;"

#---

4. Remove database:

#---

dropdb -U test_user -W test_db

#---

5. Remove user:

#---

dropuser -U postgres -W test_user

#---

6. Check user removal:

#---

psql -U test_user -d test_db -W -c "SELECT 1;"

#---

Expected:

Password for user test_user:

psql: FATAL:  password authentication failed for user "test_user"

H. Important logs:

/var/lib/pgsql/data/pg_log/*

/var/log/messages