Close

PostgreSQL – Install PostgreSQL, and then create a DB and User Account

Introduction

This guide gives you a crash course on:

  1. Installing the PostgreSQL software on your server
  2. Create your very first PostgreSQL DB - in our case we are going to create a db called "reviewdb"
  3. Create a new DB user account and give it full access to the new db
  4. Connect to the new db using the new db user account
  5. Remotely connect to the new db using the new db user account
  6. Making remote connections more secure

Installing the PostgreSQL software on your server

First download the rpm, using curl/wget. First decide on which postgresql rpm package you want. e.g.:

curl http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-oraclelinux93-9.3-1.noarch.rpm -o postgresql.rpm

then, while in that directory, install it using yum:

yum install postgresql.rpm

Alternatively we can combine the above curl+yum commands into a single command, and run the following command instead of the above 2 commands:

yum install http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-oraclelinux93-9.3-1.noarch.rpm 

This hasn't actually installed postgres yet, it only installed the postgres yum repo, which in my case is the yum repo for postgres 9.3, which is the version I am interested in:

[root@puppetmaster tmp]# ls -l /etc/yum.repos.d/ | grep "pgdg"
-rw-r--r--  1 root root  442 Apr 21  2014 pgdg-93-oraclelinux.repo

In my case, the I want to install postresql v9.3, so that's what I'll search for that.

[root@puppetmaster tmp]# yum search "postgresql93"
Loaded plugins: refresh-packagekit
========================================================================================== N/S Matched: postgresql93 ==========================================================================================
postgresql93-debuginfo.x86_64 : Debug information for package postgresql93
postgresql93-jdbc-debuginfo.x86_64 : Debug information for package postgresql93-jdbc
postgresql93-odbc-debuginfo.x86_64 : Debug information for package postgresql93-odbc
postgresql93-python-debuginfo.x86_64 : Debug information for package postgresql93-python
postgresql93.x86_64 : PostgreSQL client programs and libraries
postgresql93-contrib.x86_64 : Contributed source and binaries distributed with PostgreSQL
postgresql93-devel.x86_64 : PostgreSQL development header files and libraries
postgresql93-docs.x86_64 : Extra documentation for PostgreSQL
postgresql93-jdbc.x86_64 : JDBC driver for PostgreSQL
postgresql93-libs.x86_64 : The shared libraries required for any PostgreSQL clients
postgresql93-odbc.x86_64 : PostgreSQL ODBC driver
postgresql93-plperl.x86_64 : The Perl procedural language for PostgreSQL
postgresql93-plpython.x86_64 : The Python procedural language for PostgreSQL
postgresql93-pltcl.x86_64 : The Tcl procedural language for PostgreSQL
postgresql93-python.x86_64 : Development module for Python code to access a PostgreSQL DB
postgresql93-server.x86_64 : The programs needed to create and run a PostgreSQL server
postgresql93-test.x86_64 : The test suite distributed with PostgreSQL

  Name and summary matches only, use "search all" for everything.
[root@puppetmaster tmp]#

The two rpms we have shown in bold above are the ones we need. Also there is another one listed above which is called "postgresql93.x86_64", this installs the client side only which is useful for testing whether you can connect to your postgresql db from another machine. However the above two will also install the client side utility as well.

Hence we go ahead and install them:

[root@puppetagent01 ~]# yum install postgresql93-server postgresql93-contrib
pgdg93                                                                         | 3.7 kB     00:00
pgdg93/primary_db                                                              | 147 kB     00:01
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package postgresql93-contrib.x86_64 0:9.3.6-1PGDG.rhel6 will be installed
--> Processing Dependency: postgresql93 = 9.3.6 for package: postgresql93-contrib-9.3.6-1PGDG.rhel6.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql93-contrib-9.3.6-1PGDG.rhel6.x86_64
--> Processing Dependency: libossp-uuid.so.16()(64bit) for package: postgresql93-contrib-9.3.6-1PGDG.rhel6.x86_64
---> Package postgresql93-server.x86_64 0:9.3.6-1PGDG.rhel6 will be installed
--> Running transaction check
---> Package postgresql93.x86_64 0:9.3.6-1PGDG.rhel6 will be installed
---> Package postgresql93-libs.x86_64 0:9.3.6-1PGDG.rhel6 will be installed
---> Package uuid.x86_64 0:1.6.1-10.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

======================================================================================================
 Package                     Arch          Version                     Repository                Size
======================================================================================================
Installing:
 postgresql93-contrib        x86_64        9.3.6-1PGDG.rhel6           pgdg93                   486 k
 postgresql93-server         x86_64        9.3.6-1PGDG.rhel6           pgdg93                   4.1 M
Installing for dependencies:
 postgresql93                x86_64        9.3.6-1PGDG.rhel6           pgdg93                   1.0 M
 postgresql93-libs           x86_64        9.3.6-1PGDG.rhel6           pgdg93                   191 k
 uuid                        x86_64        1.6.1-10.el6                public_ol6_latest         53 k

Transaction Summary
======================================================================================================
Install       5 Package(s)

Total download size: 5.8 M
Installed size: 23 M
Is this ok [y/N]: y
Downloading Packages:
(1/5): postgresql93-9.3.6-1PGDG.rhel6.x86_64.rpm                               | 1.0 MB     00:03
(2/5): postgresql93-contrib-9.3.6-1PGDG.rhel6.x86_64.rpm                       | 486 kB     00:00
(3/5): postgresql93-libs-9.3.6-1PGDG.rhel6.x86_64.rpm                          | 191 kB     00:00
(4/5): postgresql93-server-9.3.6-1PGDG.rhel6.x86_64.rpm                        | 4.1 MB     00:05
(5/5): uuid-1.6.1-10.el6.x86_64.rpm                                            |  53 kB     00:00
------------------------------------------------------------------------------------------------------
Total                                                                 348 kB/s | 5.8 MB     00:17
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : postgresql93-libs-9.3.6-1PGDG.rhel6.x86_64                                         1/5
  Installing : postgresql93-9.3.6-1PGDG.rhel6.x86_64                                              2/5
  Installing : uuid-1.6.1-10.el6.x86_64                                                           3/5
  Installing : postgresql93-contrib-9.3.6-1PGDG.rhel6.x86_64                                      4/5
  Installing : postgresql93-server-9.3.6-1PGDG.rhel6.x86_64                                       5/5
  Verifying  : uuid-1.6.1-10.el6.x86_64                                                           1/5
  Verifying  : postgresql93-9.3.6-1PGDG.rhel6.x86_64                                              2/5
  Verifying  : postgresql93-server-9.3.6-1PGDG.rhel6.x86_64                                       3/5
  Verifying  : postgresql93-libs-9.3.6-1PGDG.rhel6.x86_64                                         4/5
  Verifying  : postgresql93-contrib-9.3.6-1PGDG.rhel6.x86_64                                      5/5

Installed:
  postgresql93-contrib.x86_64 0:9.3.6-1PGDG.rhel6    postgresql93-server.x86_64 0:9.3.6-1PGDG.rhel6

Dependency Installed:
  postgresql93.x86_64 0:9.3.6-1PGDG.rhel6         postgresql93-libs.x86_64 0:9.3.6-1PGDG.rhel6
  uuid.x86_64 0:1.6.1-10.el6

Complete!

Here's a quick check that postgresql's command line client at least has been installed successfully:

[root@puppetagent01 ~]# psql --version
psql (PostgreSQL) 9.3.6

Now do:

[root@puppetmaster tmp]#  service postgresql-9.3 status
postgresql-9.3 is stopped
[root@puppetmaster tmp]#  service postgresql-9.3 initdb       # this starts the db
Initializing database:                                     [  OK  ]
[root@puppetmaster tmp]# chkconfig postgresql-9.3 on          # this starts this service at boot-time
[root@puppetmaster tmp]#

Next, you start the postgres service:

[root@puppetmaster tmp]# service postgresql-9.3 start
Starting postgresql-9.3 service:                           [  OK  ]

The above installation, has made a bunch of psql commandline utilities available.

After that you need to su to the postgres user:

The "postgres" account has been automatically created during the Postgres installation. Not only is the "postgres" account a Linux account, but it is also a db-user account, which is also automatically created during the postgres installation. In fact the "postgres" user account is Postgres's equivalent of the linux's "root".

[root@puppetmaster tmp]# sudo -u postgres -i
-bash-4.1$ psql                                # then enter the postgresql command prompt. 
psql (9.3.5)
Type "help" for help.

postgres=#

Notice here that there is link between the postgres linux account and the postgres db account. Which is that if you run "psql" under the postgres linux user, then postgres, automatically assumes you want to connect using the corresponding db user account.

Notice here that we used the psql command-line utility.

Create your very first PostgreSQL DB

Before we create our first db, Let's first view a list of all PostgreSQL databases that Postgres comes with out of the box, using psql's "\l" command.:

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

Now we create our own PostgreSQL DB. To do this you need to do it from the bash-prompt (while still logged in as the postgres user), and not the psql prompt:

-bash-4.1$ createdb reviewdb

In our case, we created a db called "reviewdb"

Note if you want to, you can also delete a postgres db.

Let's now confirm that it has been created:

-bash-4.1$ psql
psql (9.3.5)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 reviewdb  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

postgres=#

Create a PostgreSQL user account

Before we create a new db user account, we should first check what db user account currently exists. There are 2 ways that you can check this, either run a sql query, or use the "\du" option:

[root@puppetagent01 ~]# su - postgres
-bash-4.1$ psql
psql (9.3.6)
Type "help" for help.

postgres=# SELECT rolname FROM pg_roles;
 rolname
----------
 postgres
 gerrit
(2 rows)

postgres=# \du
                                List of roles
 Role name    |                   Attributes                   | Member of
--------------+------------------------------------------------+-----------
 gerritdbuser |                                                | {}
 postgres     | Superuser, Create role, Create DB, Replication | {}

postgres=#

Note, you can find more info of the "\du" option like this:

-bash-4.1$ psql
psql (9.3.6)
Type "help" for help.

postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
postgres=# \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \gset [PREFIX]         execute query and store results in psql variables
  \h [NAME]              help on syntax of SQL commands, * for all commands
  \q                     quit psql
  \watch [SEC]           execute query every SEC seconds
.
.
.
  \du[+]  [PATTERN]      list roles
.
.
.

In our case, we created a user called "gerritdbuser" and set the password "admin123" and we have granted full access priveleges to the "reviewdb" db.

Now let's create a new user (which in our case will have the username "gerritdbuser") To do this we need to connect to one of postgres's pre-existing db, called "template1", in order to do this:

-bash-4.1$ id
uid=26(postgres) gid=26(postgres) groups=26(postgres)
-bash-4.1$ psql template1
psql (9.3.5)
Type "help" for help.

template1=# CREATE USER gerritdbuser WITH PASSWORD 'admin123';
CREATE ROLE
template1=# GRANT ALL PRIVILEGES ON DATABASE "reviewdb" to gerritdbuser;
GRANT
template1=#

Now let's check that the gerritdbuser account now exists:

This is a placeholder. 

Next we need to assign full control to the reviewdb db that we have created. We will do this in the next section.

Grant full DB access privelege to the new user account

This is done like this:

-bash-4.1$ id
uid=26(postgres) gid=26(postgres) groups=26(postgres)
-bash-4.1$ psql template1
psql (9.3.5)
Type "help" for help.

template1=# GRANT ALL PRIVILEGES ON DATABASE "reviewdb" to gerritdbuser;
GRANT
template1=#

Now we can check that this has been successful by viewing the last column of the "\l" command:

This is a placeholder. 

Connect to the new db with the new db user account

At this point you will have realized that when you run psql on it's own, then by default, psql will assume that you want to connect to the db with the same db-account-name as the linux account name. This means that psql will fail if you try to run psql under a linux account that doesn't have an equivalent db account with a matching name. For example we haven't created a db account called "root" hence

At this point you should now be able to run the following command as postgress/root user:

-bash-4.1$ psql -h 127.0.0.1 -U gerritdbuser -d reviewdb
psql: FATAL:  Ident authentication failed for user "gerritdbuser"

To fix this we need to edit the following file:

[root@puppetmaster data]# ls /var/lib/pgsql/9.3/data/pg_hba.conf
/var/lib/pgsql/9.3/data/pg_hba.conf

In this file, you will find the following line:

host    all             all             127.0.0.1/32            ident

Here you simply replace "ident" with "trust", and the restart the postgres service.

Now if you repeat the psql call, you get:

[root@puppetagent01 ~]# psql -h 127.0.0.1 -U gerritdbuser -d reviewdb
psql (9.3.6)
Type "help" for help.

reviewdb=>

Success!

Connecting to db server remotely

Now let's try connection using the machine ip address instead:

[root@puppetagent01 ~]# psql -h 10.1.172.11 -U gerritdbuser -d reviewdb
psql: could not connect to server: Connection refused
        Is the server running on host "10.1.172.11" and accepting
        TCP/IP connections on port 5432?
[root@puppetagent01 ~]# 

To fix this, you need to first edit the following file:

[root@puppetagent01 ~]# vi /var/lib/pgsql/9.3/data/postgresql.conf

On this file, we have the listen_addresses setting:

.
.
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost'    # what IP address(es) to listen on;
.
.

uncomment this line and set this to: '*', i.e.:

.
.
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost'    # what IP address(es) to listen on;
listen_addresses = '*'    # what IP address(es) to listen on;
.
.

Note: in my case I duplicated this line before editing it.

Then restart the postgres service and try again. You should now get the following error message:

[root@puppetagent01 ~]# psql -h 10.1.172.11 -U gerritdbuser -d reviewdb
psql: FATAL:  no pg_hba.conf entry for host "10.1.172.11", user "gerritdbuser", database "reviewdb", SSL off

Now let's edit our pg_hba.conf and add in an entry for our ip address, which in this case is "10.1.172.11". Hence we add the following line:

host    all             all             10.1.172.11/32            trust

Note: this is nearly the same as our earlier 127.0.0.1 entry.

Now restart the postgres service and try again:

[root@puppetagent01 ~]# psql -h 10.1.172.11 -U gerritdbuser -d reviewdb
psql (9.3.6)
Type "help" for help.

reviewdb=> \q
[root@puppetagent01 ~]#

Success!!!

Also see: http://dba.stackexchange.com/questions/14740/how-to-use-psql-with-no-password-prompt for better security.
=======================================================================

On the client-machine run:


yum install http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-oraclelinux93-9.3-1.noarch.rpm 

Next, we just want to install the postgres client software rather than the whole postgres db software:

[root@puppetagent03 ~]# yum search "postgresql93" | grep "^postgres" | grep "client"
postgresql93.x86_64 : PostgreSQL client programs and libraries
[root@puppetagent03 ~]#

So let's go ahead and do this now:

[root@puppetagent03 ~]# psql --version
-bash: psql: command not found
[root@puppetagent03 ~]# yum install postgresql93
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package postgresql93.x86_64 0:9.3.6-1PGDG.rhel6 will be installed
--> Processing Dependency: postgresql93-libs = 9.3.6-1PGDG.rhel6 for package: postgresql93-9.3.6-1PGDG.rhel6.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql93-9.3.6-1PGDG.rhel6.x86_64
--> Running transaction check
---> Package postgresql93-libs.x86_64 0:9.3.6-1PGDG.rhel6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

===============================================================================================================================================================================================================
 Package                                                Arch                                        Version                                                  Repository                                   Size
===============================================================================================================================================================================================================
Installing:
 postgresql93                                           x86_64                                      9.3.6-1PGDG.rhel6                                        pgdg93                                      1.0 M
Installing for dependencies:
 postgresql93-libs                                      x86_64                                      9.3.6-1PGDG.rhel6                                        pgdg93                                      191 k

Transaction Summary
===============================================================================================================================================================================================================
Install       2 Package(s)

Total download size: 1.2 M
Installed size: 5.8 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): postgresql93-9.3.6-1PGDG.rhel6.x86_64.rpm                                                                                                                                        | 1.0 MB     00:01
(2/2): postgresql93-libs-9.3.6-1PGDG.rhel6.x86_64.rpm                                                                                                                                   | 191 kB     00:00
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                                                          163 kB/s | 1.2 MB     00:07
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : postgresql93-libs-9.3.6-1PGDG.rhel6.x86_64                                                                                                                                                  1/2
  Installing : postgresql93-9.3.6-1PGDG.rhel6.x86_64                                                                                                                                                       2/2
  Verifying  : postgresql93-libs-9.3.6-1PGDG.rhel6.x86_64                                                                                                                                                  1/2
  Verifying  : postgresql93-9.3.6-1PGDG.rhel6.x86_64                                                                                                                                                       2/2

Installed:
  postgresql93.x86_64 0:9.3.6-1PGDG.rhel6

Dependency Installed:
  postgresql93-libs.x86_64 0:9.3.6-1PGDG.rhel6

Complete!
[root@puppetagent03 ~]# psql --version
psql (PostgreSQL) 9.3.6
[root@puppetagent03 ~]#

Now if we try to connect, we get the following error message:

[root@puppetagent03 ~]# psql -h 10.1.172.11 -U gerritdbuser -d reviewdb
psql: FATAL:  no pg_hba.conf entry for host "10.1.172.13", user "gerritdbuser", database "reviewdb", SSL off
[root@puppetagent03 ~]# 

This means that our client server managed to successfully contact the db server, but the db server refused the connection.

To fix this we once again edit the db-server's, pg_hba.conf file. This time we insert a line representing the client server's ip address. In my case my client's ip address is "10.1.172.13", therefore insert:

host    all             all             10.1.172.13/32            trust

This essentially add's the client server to the whitelist.

Next insert the following line in the pg_hba.conf file:

pg_hba.conf

Now lets

- Insert the following line

host    all             all             {vm-ip-number}/32            trust
host    all             all             127.0.0.1/32           trust

The next few things I think we need to do :

- Into the pg_hba.conf file:
[root@puppetmaster data]# ls /var/lib/pgsql/9.3/data/pg_hba.conf
/var/lib/pgsql/9.3/data/pg_hba.conf

- is open up IP tables to allow posgresql:

iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d {db-server-ip-number} --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s {db-server-ip-number} --sport 5432 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

Note, as soon as postgres finds a match it will stop processing this file any further. Therefore you need to ensure this file doesn't contain a match earlier, if so then comment this out.

- restart postres service to take this change into account.

automating postgresql installation and setup with puppet

All the above can be automated using puppet which. We have a seperate article for this.

Note:

http://www.cyberciti.biz/tips/howto-iptables-postgresql-open-port.html

Useful links:

http://www.postgresql.org/docs/9.3/static/tutorial-createdb.html

15 Practical PostgreSQL Database Administration Commands

http://www.cyberciti.biz/faq/psql-fatal-ident-authentication-failed-for-user/

http://www.postgresql.org/docs/9.3/static/client-authentication.html

http://www.cyberciti.biz/faq/postgresql-remote-access-or-connection/

http://www.postgresql.org/docs/9.3/static/auth-pg-hba-conf.html

http://www.cyberciti.biz/faq/psql-fatal-ident-authentication-failed-for-user/

http://www.postgresql.org/docs/9.3/static/client-authentication-problems.html

http://www.postgresql.org/docs/9.3/static/app-psql.html

http://www.postgresql.org/docs/9.3/static/reference-client.html (these are all the commandline utilities that comes with postgres)

https://wiki.postgresql.org/wiki/YUM_Installation

http://www.postgresql.org/download/linux/redhat/

http://yum.postgresql.org/repopackages.php

http://www.postgresql.org/docs/9.3/static/tutorial.html