Configuring Amazon Aurora RDS (PostgreSQL Compatible) as the Backend Database

PAM360 uses PostgreSQL as the default backend database, and it comes bundled with the product. However, you can configure the product with other database servers such as PostgreSQL, MS SQL (both standalone and cluster), Azure SQL, and Amazon RDS. Amazon Web Services (AWS) Relational Database Service (RDS) is a managed service that makes it easy to set up, operate, and scale a relational database in the cloud. It supports several database engines, including Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle Database, and Microsoft SQL Server.

Benefits of using Amazon RDS Instance

• Simplifies the database administration tasks such as automated backups, patching, and scaling.
• Ensure minimal downtime and automatic failover, along with dynamic scaling to compute storage resources to meet demand.
• Optimized storage options and read replicas enhance performance.
• Automated backups, snapshots, and multi-region deployments ensure data durability and availability.

Here, in this document, you will learn how to set up Amazon Aurora (PostgreSQL Compatible) RDS as the backend database in the following topics:

1. Creating an Amazon Aurora (PostgreSQL Compatible) RDS Instance
2. Configuring the Amazon Aurora (PostgreSQL Compatible) RDS as the Backend Database

1. Creating an Amazon Aurora (PostgreSQL Compatible) RDS Instance

1. Log in to AWS to access the AWS Management Console and navigate to Services >> Database >> RDS.
   
2. Click on Create database from the Dashboard or Database page to initiate the creation of a new RDS for PAM360.
   
3. On the database creation page, follow these high-level steps:
   1. Select the Standard create method in the database creation method.
   2. Choose the Engine type as Aurora (PostgreSQL Compatible) under the Engine options.
      

      Note: Alternatively, you can also select PostgreSQL for a standalone database.
      (Amazon Aurora (PostgreSQL-Compatible) is for higher performance, scalability, and availability with a clustered architecture and auto-scaling capabilities, whereas PostgreSQL on Amazon RDS provides a more traditional PostgreSQL experience with full feature compatibility and simpler architecture but may lack the advanced performance and scalability benefits.)

      
   3. Enter a name for the Database Cluster under the DB cluster identifier.
   4. Enter a Master username under Credential Settings.
   5. Select Self-managed for Credential management type and enter a Master password.
      
   6. Select the Cluster storage configuration and Instance configuration based on your organization's requirements and preferences.
      
   7. Configure the Availability & durability and Connectivity section based on your preferences.

      Caution: The Virtual private cloud should be the same for the PAM360 application and PostgreSQL if both are hosted in AWS. If PAM360 is on-prem or in a different VPC, create security rules to allow the traffic.

      
   8. Select the DB subnet group and Public access accordingly. Providing public access will allow the database to be accessed from anywhere on the internet.
   9. Choose an existing VPC security group or create a new one under the VPC security group (firewall).

      Note: VPC security groups serve as AWS firewall rules. The VPC security groups selected or created should be configured during creation to ensure proper connectivity between the application and the database server.

      
   10. Select an added Database authentication method if required. For our case, we will store the Master username and Master password specified earlier in the database_params.conf file inside <PAM360 Installation Directory>/conf folder for the initial password authentication.
   11. Turning on the performance insights and DevOps Guru is optional, based on your requirements.
   12. Utilize the suggested add-ons for the created RDS as required and click Close.
       
   13. This will further proceed with the RDS instance creation process, and upon creation, you can see instance details with cluster name as pam360-aws-rds-db, with pam360-aws-rds-db-instance-1 as the writer instance and pam360-aws-rds-db-instance-1-us-east-1a as a reader instance.
       
4. Upon successful instance creation, click on the View connection details to view the connection details including Master username, Master password, and the Endpoint i.e., the Hostname.
   
   
5. Copy the Endpoint value as it will be used in the database_params.conf file for further configuration.
   

   Caution: Record or make a note of the Master password as you cannot view it again. If you forget the provided master password, you will have to reset it.

2. Configuring the Amazon Aurora (PostgreSQL Compatible) RDS as the Backend Database

1. Use a database connectivity tool like pgAdmin and register the created Amazon Aurora RDS instance with the available inputs.
2. Create a new database in the Amazon Aurora RDS instance for PAM360.
3. Ensure the extension pgcrypto is available in the extension drop-down. If not, create it using the command - CREATE EXTENSION pgcrypto;
4. Confirm the extension creation by expanding the database and checking under Extensions for pgcrypto.
5. Download and install PAM360 in an AWS EC2 instance or an on-prem server.
6. Download the PostgreSQL RDS region's Root CA and import it into PAM360 using the importCert.bat command.
7. Make copies of the following files under <PAM360 Installation Directory>\conf directory and rename them:
   1. customer-config.xml to customer-config_old.xml
   2. database_params.conf to database_params_old.conf
8. From the <PAM360 Installation Directory>\conf folder, open the database_params.conf file in a text editor and perform the following actions:
   1. Update the Master username in the username field and the Master password in the password field.
   2. Update the localhost detail in the URL with the Endpoint value copied earlier.
   3. Update the port number to the actual port number of the PostgreSQL instance in AWS. By default, the PostgreSQL database in AWS RDS runs on port 5432.
   4. Search for db.password.encrypted=true and change it as db.password.encrypted=false
      
   5. Now, save and close the database_params.conf file.
9. Open the customer-config.xml file in a text editor, search for startdbserver= true and change its value to false.
10. 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:

11. 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.
12. 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"
13. Now, start the PAM360 service.

You have now successfully configured the Amazon RDS Aurora (PostgreSQL Compatible) as the backend database for PAM360.