# Configuring CMA with PostgreSQL

By default, CMA uses an embedded H2 database that requires no setup. For production
deployments or when you need a shared database, CMA supports PostgreSQL as an
alternative backend.

## Prerequisites

- PostgreSQL 12 or later
- A PostgreSQL superuser account (e.g., `postgres`) to run the setup script
- The `psql` client installed on the host where you run the setup script
- Network connectivity from the CMA Master and Agent hosts to the PostgreSQL server
- If CMA Agent and CMA Master use different PostgreSQL instances (e.g., each on
  its own cluster node), run the setup script once per PostgreSQL instance

## Step 1: Create CMA Databases

Run the setup script from the parcel directory on any host that has `psql` installed
and can reach the PostgreSQL server:

```bash
/opt/cloudera/parcels/cma/bin/setup-cma-postgres.sh \
  --host <pg-host> \
  --admin-user postgres \
  --admin-password <superuser-password>
```

> **Note:** The setup script always creates all three databases (`cma`, `cma-auth`,
> `cma-agent`). In a Master-Agent split deployment where the Agent uses a separate
> PostgreSQL instance, only the `cma-agent` database on the Agent's PostgreSQL is
> used. The unused `cma` and `cma-auth` databases can be safely ignored or dropped.

The script creates three databases and their owning users:

| Database | User | Purpose |
|----------|------|---------|
| `cma` | `cma` | CMA Master |
| `cma-auth` | `cma` (shared) | CMA Auth Server |
| `cma-agent` | `cma-agent` | CMA Agent |

Passwords are auto-generated and printed at the end. Save them for configuring CMA.

### Setup Script Options

| Option | Default | Description |
|--------|---------|-------------|
| `--host` | *(required)* | PostgreSQL hostname or IP |
| `--admin-user` | *(required)* | PostgreSQL superuser (e.g., `postgres`) |
| `--admin-password` | *(required)* | PostgreSQL superuser password |
| `--port` | `5432` | PostgreSQL port |
| `--cma-user` | `cma` | CMA Master database user |
| `--cma-password` | *(auto-generated)* | CMA Master database user password |
| `--auth-user` | *(same as `--cma-user`)* | CMA Auth database user |
| `--auth-password` | *(same as `--cma-password`)* | CMA Auth database user password |
| `--agent-user` | `cma-agent` | CMA Agent database user |
| `--agent-password` | *(auto-generated)* | CMA Agent database user password |

### Manual Database Creation

If you prefer to create the databases manually instead of using the setup script:

```sql
sudo -u postgres psql

CREATE USER cma WITH PASSWORD 'your_secure_password';
CREATE USER "cma-agent" WITH PASSWORD 'your_agent_password';

CREATE DATABASE cma OWNER cma;
CREATE DATABASE "cma-auth" OWNER cma;
CREATE DATABASE "cma-agent" OWNER "cma-agent";

GRANT ALL PRIVILEGES ON DATABASE cma TO cma;
GRANT ALL PRIVILEGES ON DATABASE "cma-auth" TO cma;
GRANT ALL PRIVILEGES ON DATABASE "cma-agent" TO "cma-agent";

\c cma
GRANT ALL ON SCHEMA public TO cma;
\c cma-auth
GRANT ALL ON SCHEMA public TO cma;
\c cma-agent
GRANT ALL ON SCHEMA public TO "cma-agent";
```

## Step 2: Configure CMA

### Parcel Deployment (Cloudera Manager)

In Cloudera Manager, configure the following parameters for each CMA service:

**CMA Master service configuration:**

| Parameter | Value |
|-----------|-------|
| Database Type | `postgres` |
| Database Host | PostgreSQL hostname or IP |
| Database Port | `5432` (or your custom port) |
| Database User | `cma` |
| Database Password | Password from setup script |

**CMA Agent service configuration:**

| Parameter | Value |
|-----------|-------|
| Database Type | `postgres` |
| Database Host | PostgreSQL hostname or IP |
| Database Port | `5432` (or your custom port) |
| Database User | `cma-agent` |
| Database Password | Password from setup script |

Then restart both CMA services.

### Local or Docker Deployment

CMA generates credential files (`db.properties` and `auth-db.properties`) automatically during startup from the environment variables below. These files are loaded by Spring Boot via `--spring.config.additional-location`.

Set the database connection parameters using environment variables:

```bash
# CMA Master
export CMA_DB_TYPE=postgres
export CMA_DB_HOST=db.example.com
export CMA_DB_PORT=5432
export CMA_DBA_USER=cma
export CMA_DBA_PASS=your_secure_password

# Auth Server (defaults to Master values if not set)
export CMA_AUTH_DB_HOST=db.example.com
export CMA_AUTH_DB_NAME=cma-auth
export CMA_AUTH_DBA_USER=cma
export CMA_AUTH_DBA_PASS=your_secure_password

cma-4.1.1-b12/bin/cma start
```

For the CMA Agent (if running locally):

```bash
export CMA_DB_TYPE=postgres
export CMA_AGENT_DB_HOST=db.example.com
export CMA_AGENT_DB_PORT=5432
export CMA_AGENT_DBA_USER=cma-agent
export CMA_AGENT_DBA_PASS=your_agent_password
```

## Environment Variables Reference

### CMA Master

| Variable | Default | Description |
|----------|---------|-------------|
| `CMA_DB_TYPE` | `h2` | Database backend: `h2` (embedded) or `postgres` (external PostgreSQL) |
| `CMA_DB_HOST` | — | PostgreSQL hostname (required when `CMA_DB_TYPE=postgres`) |
| `CMA_DB_PORT` | `5432` | PostgreSQL port |
| `CMA_DB_NAME` | `cma` | Database name for CMA Master |
| `CMA_DBA_USER` | `cma` | Database username |
| `CMA_DBA_PASS` | — | Database password (**mandatory** for all database types, including H2) |

### CMA Auth Server

| Variable | Default | Description |
|----------|---------|-------------|
| `CMA_AUTH_DB_HOST` | `CMA_DB_HOST` | PostgreSQL hostname for auth server |
| `CMA_AUTH_DB_PORT` | `CMA_DB_PORT` | PostgreSQL port for auth server |
| `CMA_AUTH_DB_NAME` | `cma-auth` | Database name for auth server |
| `CMA_AUTH_DBA_USER` | `CMA_DBA_USER` | Auth database username |
| `CMA_AUTH_DBA_PASS` | `CMA_DBA_PASS` | Auth database password |

### CMA Agent

| Variable | Default | Description |
|----------|---------|-------------|
| `CMA_AGENT_DB_HOST` | — | PostgreSQL hostname for agent (required when `CMA_DB_TYPE=postgres`) |
| `CMA_AGENT_DB_PORT` | `5432` | PostgreSQL port for agent |
| `CMA_AGENT_DB_NAME` | `cma-agent` | Database name for agent |
| `CMA_AGENT_DBA_USER` | `cma-agent` | Agent database username |
| `CMA_AGENT_DBA_PASS` | — | Agent database password (required when `CMA_DB_TYPE=postgres`) |

## Schema Management

CMA uses Liquibase for automatic schema management. When starting with the
`postgres` profile, Liquibase creates all required tables, indexes, and seed data
automatically. No manual schema creation is needed.

## Deployment Topologies

### Single cluster (Master + Agent co-located)

All three databases (`cma`, `cma-auth`, `cma-agent`) on a single PostgreSQL
instance. Run the setup script once. Configure both services to point to the
same PostgreSQL host.

### Master-Agent split (separate clusters)

CMA Master on the target cluster, CMA Agent on the source cluster. Each can
use a local PostgreSQL instance:

- **Target cluster PostgreSQL**: `cma`, `cma-auth`, `cma-agent` databases
  (the target Agent and Master share the same PostgreSQL)
- **Source cluster PostgreSQL**: `cma-agent` database only

Run the setup script on each PostgreSQL host. Configure the CMA Agent on the
source cluster with the `CMA Master URL` pointing to the Master's gRPC port
(default 8091) on the target cluster.

When the CMA Agent is on a different cluster from the Master, the
`CMA Master URL` parameter must be set in the Agent's service configuration in
Cloudera Manager (format: `http://<master-host>:<grpc-port>`).

## Limitations

- **No H2-to-PostgreSQL migration**: Switching from H2 to PostgreSQL requires a
  fresh start. Existing H2 data cannot be migrated automatically.
- **Separate databases**: CMA Master, the Auth Server, and CMA Agent each use
  separate databases. All must be configured when using PostgreSQL.

## Switching from H2 to PostgreSQL

When switching an existing CMA deployment from H2 to PostgreSQL, the Master starts
with a fresh database. CMA Agents, however, retain their local H2 databases which
contain stale server and cluster IDs referencing the old Master database. This
causes agents to fail registration because those IDs no longer exist.

To resolve this, wipe the agent H2 databases on **every** agent host after switching
the Master to PostgreSQL:

```bash
# On each CMA Agent host:
# 1. Stop the CMA Agent service (via CM or systemctl)

# 2. Delete the agent's local H2 database
rm -f /home/cma-agent/data/db/cma-agent.mv.db

# 3. Start the CMA Agent service
```

After restart, each agent will re-register with the Master and receive new IDs.

Similarly, if the Master was previously running with H2 and accumulated data
(`/var/lib/cma/data/`, `/var/lib/cma/config/`), clear those directories before
switching to PostgreSQL to avoid H2 startup errors:

```bash
# On the CMA Master host (stop service first):
sudo rm -rf /var/lib/cma/data/* /var/lib/cma/config/*
```

## Troubleshooting

### PostgreSQL superuser password not known

Cloudera Manager's PostgreSQL typically uses `md5` authentication for all
connections. If you don't have the `postgres` superuser password, you can
temporarily enable `peer` authentication (OS-level user matching) to set one:

```bash
# Add peer auth as the first rule (takes priority)
sudo sed -i '1i local   all   postgres   peer' /var/lib/pgsql/12/data/pg_hba.conf
sudo systemctl reload postgresql-12

# Set a known password
sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'new_password';"

# Remove the peer auth rule
sudo sed -i '1d' /var/lib/pgsql/12/data/pg_hba.conf
sudo systemctl reload postgresql-12
```

### Remote connections rejected

If the CMA Agent connects to a PostgreSQL on a different host and gets
"connection refused" or authentication errors, verify:

1. PostgreSQL listens on all interfaces (`listen_addresses = '*'` in
   `postgresql.conf`)
2. `pg_hba.conf` allows remote connections:
   ```
   host    all    all    0.0.0.0/0    md5
   ```
3. Reload PostgreSQL after changes: `sudo systemctl reload postgresql-12`

### Connection refused or hostname resolution error

Verify the PostgreSQL host is reachable from the CMA host:

```bash
psql -h <pg-host> -p <pg-port> -U <user> -d postgres -c "SELECT 1"
```

Check that `pg_hba.conf` on the PostgreSQL server allows connections from the CMA hosts.

### Missing Database Host or Password error on startup

When `CMA_DB_TYPE` is set to `postgres`, the `Database Host` and `Database Password`
parameters are required. In Cloudera Manager, verify these are set in the CMA service
configuration. For local/Docker deployments, ensure the corresponding environment
variables (`CMA_DB_HOST`, `CMA_DBA_PASS` for Master; `CMA_AGENT_DB_HOST`,
`CMA_AGENT_DBA_PASS` for Agent) are exported before starting.

### Parcel deployment: environment variable injection via safety valve

In parcel deployments, CMA reads database parameters from the CM-managed service
configuration. If additional environment variables are needed (e.g., for the Auth Server),
you can inject them using CM safety valves.

In Cloudera Manager, set the following for the **CMA_AUTH_SERVER** role:

**CMA_AUTH_SERVER > Configuration > CMA_AUTH_SERVER role Environment Advanced Configuration Snippet (Safety Valve)**:

```
CMA_AUTH_DB_HOST=db.example.com
CMA_AUTH_DB_PORT=5432
CMA_AUTH_DB_NAME=cma-auth
CMA_AUTH_DBA_USER=cma
CMA_AUTH_DBA_PASS=your_secure_password
```

Safety valve values are injected directly into the Java process environment by CM,
bypassing any shell script issues. Restart the CMA service after applying these
changes.
