Install/Configure Postgresql on Centos 6

| Comments

Configure/Add Postgres Repo

  1. Download the Centos 6 Repo RPM from HERE
1
wget http://yum.pgrpms.org/9.1/redhat/rhel-6-x86_64/pgdg-redhat91-9.1-5.noarch.rpm
  1. Install the Repo RPM
1
sudo rpm -Uvh pgdg-redhat91-9.1-5.noarch.rpm

Install Postgresql Server

Now that you have the repo install you can use YUM to install Postgresql from the PG repo

1
sudo yum install postgresql91-server

Initialize & Start Postgresql Server

You’ll first need to initalize the database for postgresql. If you attempt to start the service before initializing that database you’ll get an error like this….

1
2
3
smbambling@pg1 ~]$ sudo /etc/init.d/postgresql-9.1 start
/var/lib/pgsql/9.1/data is missing. Use "service postgresql-9.1.3 initdb" to initialize the cluster first.
                                            [FAILED]

To initialize the database you can call initdb the init script

1
2
[smbambling@pg1 ~]$ sudo /etc/init.d/postgresql-9.1 initdb
Initializing database:                                     [  OK  ]

Once the initialization is sucessfull you’ll configuration database files created in /var/lib/pgsql/9.1/data

1
2
3
[root@pg1 data]# ls
base    pg_clog      pg_ident.conf  pg_multixact  pg_serial    pg_subtrans  pg_twophase  pg_xlog          postmaster.opts
global  pg_hba.conf  pg_log         pg_notify     pg_stat_tmp  pg_tblspc    PG_VERSION   postgresql.conf  postmaster.pid

Now you will be able to successfully start the database with the init script

1
2
[smbambling@pg1 ~]$ sudo /etc/init.d/postgresql-9.1 start
Starting postgresql-9.1 service:                           [  OK  ]

To veryify that the service is running you can by issing status from the init script

1
2
[smbambling@pg1 ~]$ sudo /etc/init.d/postgresql-9.1 status
 (pid  22444) is running...

Configure Postgresql Access Permissions

Set the authentication method

When you called the initdb command above from RedHat’s init script it configured permissions on the database. These configuration settings are pg_hba.conf

RedHat calls the initdb like this:

1
initdb --pgdata='$PGDATA' --auth='ident sameuser'

This uses the not so popular ident scheme to determine if a user is allow to connect to the database.

ident: An authentication schema that relies on the currently logged in user. If you’ve su -s to postgres and then try to login as another user, ident will fail (as it’s not the currently logged in user).

This can be a sore spot if your not aware how it was configured and will give an error if trying to create a database with a user that is not currently logged into the system.

1
createdb: could not connect to database postgres: FATAL:  Ident authentication failed for user "myUser"

To get around this issue you can modify the pg_hba.conf file to to move from the ident scheme to the md5 scheme

1
2
3
4
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
1
2
3
4
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Create “Super User”

By default only the postgres user on the system can create databases and manage the server. This user is granted superuser privilegdges to the postgres database(s) and server.

To work around this we will create an additional userser with superuser priviledges for management.

Its advised NOT to grant an application user superuser priviledges for security

To create users in postgres you can use the CREATE ROLE and priviledges can be modified with ALTER ROLE. To assist with user creation postgres provides a wrapper script createuser

Only superusers and users with CREATEROLE privilege can create new users, so creating the initial user must be done from the postgres account.

  1. Become the postgres user
  2. Invoke the createuser script with -p option. -p will issue a prompt for the password of the new user
    • Enter a username for the new user. We are entering root here.
    • When prompted grant the user superuser priviledges
1
2
3
4
5
6
7
[smbambling@pg1 ~]$ sudo su - postgres
-bash-4.1$ createuser -P
Enter name of role to add: root
Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) y
-bash-4.1$ exit

Comments