# Configuring Google Cloud SQL for PostgreSQL as the Backend Database

PAM360 uses bundled PostgreSQL as the default backend database. However, you can configure the product with other database servers such as PostgreSQL cluster, MS SQL (both standalone and cluster), Azure SQL, Amazon RDS, and Google Cloud SQL. Google Cloud SQL is a fully managed relational database service that makes it easy to set up, maintain, manage, and administer your PostgreSQL databases on Google Cloud.

## Benefits of using Google Cloud SQL Instance

- Automates backups, replication, patches, and updates to keep your database reliable and up to date.
- Ensures high availability with automatic failover and regional persistent disk storage for data durability.
- Supports seamless vertical and horizontal scaling of compute and storage resources to meet demand.
- Provides built-in encryption at rest and in transit, along with IAM-based access control for enhanced security.

**Caution**

Do not start the PAM360 service after installation. The service should only be started after the database configuration steps below are completed.

Here, in this document, you will learn how to set up Google Cloud SQL for PostgreSQL as the backend database in the following topics:

1. [Creating a Google Cloud SQL for PostgreSQL Instance](https://www.manageengine.com/privileged-access-management/help/google-cloud-pgsql.html#Creating_a_Google_Cloud_SQL_for_PostgreSQL_Instance)
2. [Configuring Google Cloud SQL for PostgreSQL as the Backend Database for PAM360](https://www.manageengine.com/privileged-access-management/help/google-cloud-pgsql.html#Configuring_Google_Cloud_SQL_for_PostgreSQL_as_Backend_Database)

**Additional Details**

Please note that the settings and configurations provided during the database setup serve as educational samples. You have the flexibility to customize these configurations according to your specific needs. If you encounter any challenges while customizing these settings, please do not hesitate to reach our [support team](https://www.manageengine.com/privileged-access-management/support.html?new-homepage) for further assistance and clarification.

## 1. Creating a Google Cloud SQL for PostgreSQL Instance

1. Sign in to [Google Cloud](https://console.cloud.google.com) using your Google account.
2. In the left navigation menu, go to **Cloud SQL >> Instances** and click **New Instance** under **Create Instance** on the **Instances** page.
3. On the **Choose your database engine** page that appears, **Choose PostgreSQL**.

   ![google-cloud-db-5](https://cdn.manageengine.com/sites/meweb/images/privileged-access-management/help/google-cloud-db-1.webp)

4. On the **Create a PostgreSQL instance** page that appears, follow these steps:
5. Under the Instance info, select the **Database version,** enter a unique identifier (**Instance ID**) for your Cloud SQL instance (e.g., pam360-pgsql) and set a strong password for the default PostgreSQL superuser account postgres.

   **Additional Details**

   - PostgreSQL 15 or higher database version is recommended for PAM360 compatibility.
   - The password will be used when connecting to the SQL server using the postgres superuser account.

   **Caution**

   Store this password securely. It is required for administrative access to the Cloud SQL instance and for creating databases for PAM360.

   ![google-cloud-db-6](https://cdn.manageengine.com/sites/meweb/images/privileged-access-management/help/google-cloud-db-2.webp)

6. Select the **Region** closest to the geographic location of your PAM360 server to minimize network latency.
7. Under **Customize your instance >> Machine configuration**, select a configuration suitable for your workload. For production PAM360 deployments, a minimum of 4 vCPUs and 16 GB memory is recommended.
8. Under **Storage**, select **SSD** for better performance and set the **Storage capacity** as per your production requirements. **Enable automatic storage increases** to prevent the database from running out of space as PAM360 data grows.
9. Under **Connections**, configure how the Cloud SQL instance will be accessed:
   - **Private IP**: Enable this option to allow connectivity from within the same VPC network. This is the recommended approach for secure internal communication between the PAM360 server and the Cloud SQL instance.
   - **Public IP**: Enable this option if the Cloud SQL instance needs to be accessed from outside Google Cloud, such as from a remote or on-premises machine hosting PAM360. Under **Authorized networks**, click **Add a network** and enter the external IP address of the remote machine (or VM instance) that will access the Cloud SQL instance. This restricts access to only the specified IP addresses.

     **Best Practices**

     It is strongly recommended to use **Private IP** for production deployments to avoid exposing the database to the public internet. If Public IP is used, ensure only the specific IP addresses of authorized machines are added under Authorized networks.

     ![google-cloud-db-7](https://cdn.manageengine.com/sites/meweb/images/privileged-access-management/help/google-cloud-db-3.webp)

10. Click **Create instance**.

The provisioning process may take a few minutes. Once complete, the instance status will change to Runnable.

## 2. Configuring Google Cloud SQL for PostgreSQL as the Backend Database

1. Connect to the Cloud SQL for PostgreSQL instance using the superuser credentials (postgres) via a PostgreSQL client (e.g., pgAdmin, DBeaver, or the psql command-line tool).
2. Run the following command to create a dedicated database for PAM360:

   ```sql
   CREATE DATABASE pam360;
   ```

3. Optionally, create a dedicated database user for PAM360 and grant the required privileges:

   ```sql
   CREATE USER pam360user WITH PASSWORD 'your_password';
   GRANT ALL PRIVILEGES ON DATABASE pam360 TO pam360user;
   ```

4. Navigate to the PAM360 installation directory and open the **database_params.conf** file located in the **conf** folder.
5. Update the following parameters with your Cloud SQL instance details and save the file:
   - **username** - The database username (e.g., pam360user or postgres)
   - **password** - The password for the database user
   - **host** - The IP address of the Cloud SQL instance (Private IP or Public IP as configured)
   - **db_name** - The name of the database created for PAM360 (e.g., pam360)
   - **db.password.encrypted** - Set to **false** since the password is being entered in plain text

   **Additional Details**

   PAM360 will automatically encrypt the plain-text password in the configuration file upon the first successful startup.

6. Navigate to the PAM360 installation directory and open the **customer-config.xml** file located in the **conf** folder.
7. Locate the **StartDBServer** parameter, change its value from true to **false** and save the file. This instructs PAM360 not to start the bundled PostgreSQL database server, as the external Google Cloud SQL instance will be used instead.
8. Now, start the PAM360 service.

If you have started the PAM360 application service, even once with the default PostgreSQL database before, proceed with the following additional steps:

1. Move/delete the **manage_key.conf** and **pam360_key.key** from the **<PAM360 Installation Directory>/conf** folder. Ensure no copies of these files are present inside the **conf** folder.
2. Make a copy of **server.xml** present inside **<PAM360 Installation Directory>\conf** directory and make the following changes:
   1. Search for the keyword **keystorepass=** and change the **keystorepass** to **passtrix**, so it should be **keystorepass="passtrix"**.
   2. Also, change the entry **keystorePassEncrypted="true"** to **keystorePassEncrypted="false"**
3. Now, start the PAM360 service.

You have now successfully configured the Google Cloud SQL for PostgreSQL as the backend database for PAM360.