Cloudera External DB Configuration
How to create users for external database in Cloudera
Prashant Raghava
Let’s create Databases for Activity Monitor, Reports Manager, Hive Metastore Server, Hue Server, Sentry Server, Cloudera Navigator Metadata Server Cloudera Navigator Audit Server.
Note: We will use PostgrSQL as an external database and RHEL 7 OS.
Create databases and user accounts for components that require databases:
- If you are not using the Cloudera Manager installer, the Cloudera Manager Server.
- Cloudera Management Service roles:
- Activity Monitor (if using the MapReduce service)
- Reports Manager
- Each Hive metastore
- Sentry Server
- Cloudera Navigator Audit Server
- Cloudera Navigator Metadata Server
You can create these databases on the host where the Cloudera Manager Server will run, or on any other hosts in the cluster. For performance reasons, you should install each database on the host on which the service runs, as determined by the roles you assign during installation or upgrade. In larger deployments or in cases where database administrators are managing the databases the services use, you can separate databases from services, but use caution.
The database must be configured to support UTF-8 character set encoding.
Record the values you enter for database names, usernames, and passwords. The Cloudera Manager installation wizard requires this information to correctly connect to these databases.
“Make sure that the data directory, which by default is /var/lib/postgresql/data/, is on a partition that has sufficient free space.“
Please follow below steps to create database and users.
- Connect to PostgreSQL:
$ sudo -u postgres psql
- If you are not using the Cloudera Manager installer, create a database for the Cloudera Manager Server. The database name, username, and password can be any value.
postgres=# CREATE ROLE scm LOGIN PASSWORD 'scm'; postgres=# CREATE DATABASE scm OWNER scm ENCODING 'UTF8';
Create databases for Activity Monitor, Reports Manager, Hive Metastore Server, Hue Server, Sentry Server, Cloudera Navigator Audit Server, and Cloudera Navigator Metadata Server:
postgres=# CREATE ROLE user LOGIN PASSWORD 'password'; postgres=# CREATE DATABASE databaseName OWNER user ENCODING 'UTF8';
where user, password, and databaseName can be any value. Similarly create DB and users as shown in below table. The examples shown match the default names provided in the Cloudera Manager configuration settings:
| Role | Database | User | Password |
|---|---|---|---|
| Activity Monitor | amon | amon | amon_password |
| Reports Manager | rman | rman | rman_password |
| Hive Metastore Server | metastore | hive | hive_password |
| Sentry Server | sentry | sentry | sentry_password |
| Cloudera Navigator Audit Server | nav | nav | nav_password |
| Cloudera Navigator Metadata Server | navms | navms | navms_password |
For PostgreSQL 8.2.23 or higher, also run:
postgres=# ALTER DATABASE Metastore SET standard_conforming_strings = off;
Create Hue user and DB
Create databases for Activity Monitor, Reports Manager, Hive Metastore Server, Hue Server, Sentry Server, Cloudera Navigator Audit Server, and Cloudera Navigator Metadata Server:
DB Hostname = :5432
DB Type = DB Name = hue_d
Username = hue_u
Password = Make sure you edit config file too
vi /var/lib/pgsql/data/pg_hba.conf
Configure pg_hba.conf to set authentication methods:
# TYPE DATABASE USER CIDR-ADDRESS METHOD local all all trust # Remote access host all all 127.0.0.1/32 password # IPv4 host all all ::1/128 password # IPv6 host hue_d hue_u 0.0.0.0/0 md5
Create hue_d database and grant privileges to the hue_u user
Execute following commands on the database
$sudo -u postgres psql # login to postgres postgres=# create database hue_db with lc_collate='en_US.UTF-8'; CREATE DATABASE postgres=# create user hue_u with password 'hue@123'; CREATE ROLE postgres=# grant all privileges on database hue_db to hue_u; GRANT postgres=# \du List of users
Verify the connection to the hue_d database.
$psql -h localhost -U hue_u -d hue_db Password for user hue_u: hue=> \q # quit
Connect Hue Service to PostgreSQL
1. Stop Hue Service
- In Cloudera Manager, navigate to Cluster > Hue.
- Select Actions > Stop.
2. Connect to New Database
- Go to Hue > Configuration.
- Filter by category, Database.
- Set the following database parameters
base
DB Hostname = ::5432 DB Type = PostgreSQL DB Name = hue_d Username = hue_u Password =
- Click Save changes
3. Start Hue service
- Navigate to Cluster > Hue, if not already there.
- Select Actions > Start.
- Click Start.
- Click Hue Web UI to log on to Hue with a custom PostgreSQL database.