Configuring Oozie to Use SCM PostgreSQL database

This is a CDH5 cluster managed by CM5.
The goal is to use PostgreSQL database used by CM5 for oozie.
Refer to :
Oozie configuration guide
Manage Oozie service using Cloudera Manager

1. Create PostgreSQL user "oozie".

CREATE ROLE oozie LOGIN ENCRYPTED PASSWORD 'oozie' 
NOSUPERUSER INHERIT CREATEDB NOCREATEROLE;

2. Create PostgreSQL database "oozie".

CREATE DATABASE "oozie" WITH OWNER = oozie
 ENCODING = 'UTF8'
 TABLESPACE = pg_default
 LC_COLLATE = 'en_US.UTF8'
 LC_CTYPE = 'en_US.UTF8'
 CONNECTION LIMIT = -1;

3. Make sure postgresql.conf has set listen_addresses property to * and standard_conforming_strings is set to off.

template1=# show  standard_conforming_strings;
 standard_conforming_strings
-----------------------------
 off
(1 row)

template1=# show listen_addresses;
 listen_addresses
------------------
 *
(1 row)

4. Configure PostgreSQL to accept network connections for user oozie.

Add below line in pg_hba.conf:

host    oozie         oozie         0.0.0.0/0             md5

Reload the configuration:

sudo -u cloudera-scm pg_ctl reload -s -D /var/lib/cloudera-scm-server-db/data

From the postgreSQL log, you can see:

LOG:  received SIGHUP, reloading configuration files

5. Test the connection using "oozie" database user.

psql -p 7432 -U oozie oozie

6. Shutdown Oozie server using Cloudera Manager.

7. Change below configurations throught Cloudera Manager for service "oozie"

Oozie Server database type => postgresql
Oozie Server database name => oozie
Oozie Server database host => localhost:7432
Oozie Server database user => oozie
Oozie Server database password => oozie
"Save Changes".

8. Creating the Oozie Database Schema using Cloudera Manager

"Action"=>"Create Database"
Then you can find below tables created:

oozie=> \dt
                List of relations
 Schema |          Name          | Type  | Owner
--------+------------------------+-------+-------
 public | bundle_actions         | table | oozie
 public | bundle_jobs            | table | oozie
 public | coord_actions          | table | oozie
 public | coord_jobs             | table | oozie
 public | oozie_sys              | table | oozie
 public | openjpa_sequence_table | table | oozie
 public | sla_events             | table | oozie
 public | sla_registration       | table | oozie
 public | sla_summary            | table | oozie
 public | validate_conn          | table | oozie
 public | wf_actions             | table | oozie
 public | wf_jobs                | table | oozie
(12 rows)

9. Start Oozie server using Cloudera Manager.