Tuesday, 27 June 2017

Installing and setting up Postgres with Centos 7

The following will outline how to quickly (and securely) get postgres and up and running.

We'll firstly need to add the epel repositories with:

sudo yum install epel-release

and then install postgres with:

sudo yum install postgresql postgresql-server postgresql-contrib

Initialise the database with:

postgresql-setup initdb

Let's firstly ensure that only local applications can access the server:

sudo vi /var/lib/pgsql/data/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local        all                        postgres                                            peer
local        all                        all                                                     peer
host         all                        all                  127.0.0.1/32               md5
host         all                        all                  ::1/128                       md5

The first line permit permit unix domain socket access for the postgres user - while the second and third line provide network access to localhost IPv4 and IPv6.

Enable and start the service:

sudo systemctl enable postgresql
sudo service start postgresql

Unlike MySQL / MariaDB - postgres by default uses 'peer' accounts - that are simply local user accounts that have been mapped with a corresponding postgres account.

By default postgres create a user called 'postgres' which we can use to access it's CLI:

sudo su - postgres

psql

quit the console using '\q'

We will then create a new user for our application (from the shell) with:

createuser --interactive

(ensuring the user is NOT a super admin, however can create databases.)

and set a password for the user:

psql

\password <username>
\q

We can this test this from shell with:

psql -U <username> -h localhost -W

* The -'h' switch is important here! It can also be set as an environment variable e.g.

PGHOST=localhost
export PGHOST

0 comments:

Post a Comment