Configuring basic PostgreSQL server

<yambe:breadcrumb>Postgresql_configuration|Postgresql configuration</yambe:breadcrumb>

Configuring basic PostgreSQL server

Initiliazing database

Before starting postgresql service for first time, a postgreSQL database must be initialized. To initialize a new database use:

service postgresql initdb

Initializing database on CentOS 7.0

On CentOS 7.0 to initialize postgreSQL database and start postgresql server use:

postgresql-setup initdb
systemctl start postgresql.service

Configuring security

By default postgresql uses ident based security which means a Linux user named 'X' gets recognized as postgreSQL user named 'X' without requiring any password. But for many cases password based authentication is preferred so that if an application has a username and a password, it can authenticate itself without worrying about user privileges with which the application is running. To configure postgresql for password based authentication for all databases except 'postgres' use:

1. Edit file '/var/lib/pgsql/data/pg_hba.conf'
2. Modify the access configuration at the end of file so that it has following contents

   # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
   # "local" is for Unix domain socket connections only
   local  postgres         all                               ident
   # IPv4 local connections:
   host    postgres         all         127.0.0.1/32          ident
   # IPv6 local connections:
   host    postgres         all         ::1/128               ident
   # "local" is for Unix domain socket connections only
   local   all         all                               password
   # IPv4 local connections:
   host    all         all         127.0.0.1/32          password
   # IPv6 local connections:
   host    all         all         ::1/128               password
   

   Remove space from 1st column of all lines
3. Use 'service postgresql restart'

Configuring application specific username, database and password

For isolation among various applications interacting with same PostgreSQL server, it is often desired to have a separate database, username and password for each application. To create an application specific database and credentials use:

1. Login as root user on server machine
2. Use 'su - postgres' to login as postgres user
3. Use 'psql' command to get postgresql shell
4. Use following query to create an application specific user:

   CREATE USER <user-name> WITH NOSUPERUSER LOGIN ENCRYPTED PASSWORD '<password>';

5. Use following query to create database owned by specific user:

   CREATE DATABASE <database-name> WITH OWNER=<user-name>;

6. Use '\q' to quit 'psql' command line
7. Use 'exit' to terminate su session as postgres user
8. Try 'psql -U <user-name> -d <database-name>' command to verify that application specific settings are working

<yambe:breadcrumb>Postgresql_configuration|Postgresql configuration</yambe:breadcrumb>