Difference between revisions of "Configuring basic PostgreSQL server"

From Notes_Wiki
m
m
 
Line 1: Line 1:
[[Main Page|Home]] > [[CentOS]] > [[CentOS 6.x]] >> [[Postgresql configuration|PostgreSQL configuration]] >> [[Configuring basic PostgreSQL server]]
[[Main Page|Home]] > [[CentOS]] > [[CentOS 6.x]] > [[Postgresql configuration|PostgreSQL configuration]] > [[Configuring basic PostgreSQL server]]


==Initiliazing database==
==Initiliazing database==
Line 63: Line 63:




[[Main Page|Home]] > [[CentOS]] > [[CentOS 6.x]] >> [[Postgresql configuration|PostgreSQL configuration]] >> [[Configuring basic PostgreSQL server]]
[[Main Page|Home]] > [[CentOS]] > [[CentOS 6.x]] > [[Postgresql configuration|PostgreSQL configuration]] > [[Configuring basic PostgreSQL server]]

Latest revision as of 07:18, 6 March 2022

Home > CentOS > CentOS 6.x > PostgreSQL configuration > 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



Home > CentOS > CentOS 6.x > PostgreSQL configuration > Configuring basic PostgreSQL server