New Zealand | Encrypting the password of the PostgreSQL database used by AAP Controllers

Sebastian Baszcyj - 17.03.202320230317

Encrypting the password of the PostgreSQL database used by AAP Controllers

New Zealand | Encrypting the password of the PostgreSQL database used by AAP Controllers

Passwords within the AAP configuration files are stored in an unencrypted format. However, the files under /etc/tower/conf.d/ are only readable by the root user and awx group, but once the user has the elevated permissions, they can easily access the password used for the database.  

To eliminate the risk of storing the password in a clear text format, the password can be converted to a hash. The following procedure outlines all required steps to update the password on AAP controller nodes for postgresql database.  

  • Open the SSH session to all controller nodes (if the AAP has been configured in a cluster configuration) 
  • Elevate to the root
  • Create a backup directory under /root on all nodes
# mkdir -p /root/backup 
  • Copy the original /etc/tower/conf.d/postgres.py file to /root/backup directory on all controller nodes 
# cp /etc/tower/conf.d/postgres.py /root/backup 
  • As root, run the following command on one of the controller nodes:  
# awx-manage shell_plus 

You can expect output similar to the following:

ython 3.9.13 (main, Nov  9 2022, 13:16:24)   [GCC 8.5.0 20210514 (Red Hat 8.5.0-15)] on linux   Type "help", "copyright", "credits" or "license" for more information.   (InteractiveConsole)   >>> 
  • In the shell, type the following commands. Replace YOUR_DB_PASSWORD with the password used for AAP Database. The print command will return the encrypted hash value 
>>> from awx.main.utils import encrypt_value, get_encryption_key   >>> postgres_secret = encrypt_value('YOUR_DB_PASSWORD')   >>> print(postgres_secret)   $encrypted$UTF8$AESCBC$Z0FBQUFBQmtCbjNxcE56VzZ3SmU3d2VvMDc1T1RQeGhnampxWEpzX2J3alRuMVZFMm9IQkZ1bEQ2RW9OREUwbXI0UG9XNmZWRU1TOTZzN2hyJUZ3I4LVczR2xaQlE9PQ==   >>> exit() 
  • Save the encrypted value
  • Stop controller services on all nodes, using the following command:  
# automation-controller-service stop 
  • On each AAP controller node, navigate to /etc/tower/conf.d
  • Edit the postgres.py file and change it from the original format: 
>>> from awx.main.utils import encrypt_value, get_encryption_key   >>> postgres_secret = encrypt_value('YOUR_DB_PASSWORD')   >>> print(postgres_secret)   $encrypted$UTF8$AESCBC$Z0FBQUFBQmtCbjNxcE56VzZ3SmU3d2VvMDc1T1RQeGhnampxWEpzX2J3alRuMVZFMm9IQkZ1bEQ2RW9OREUwbXI0UG9XNmZWRU1TOTZzN2hyJUZ3I4LVczR2xaQlE9PQ==   >>> exit() 
  • Save the encrypted value 
  • Stop controller services on all nodes, using the following command:
# automation-controller-service stop 
  • On each AAP controller node, navigate to /etc/tower/conf.d  
  • Edit the postgres.py file and change it from the original format:
# Ansible Automation Platform controller database settings.      DATABASES = {      'default': {          'ATOMIC_REQUESTS': True,          'ENGINE': 'awx.main.db.profiled_pg',          'NAME': 'awx',          'USER': 'awx',          'PASSWORD': """YOUR_DB_PASSWORD""",          'HOST': 'db.example.net',          'PORT': '5432',          'OPTIONS': { 'sslmode': 'prefer',                       'sslrootcert': '/etc/pki/tls/certs/ca-bundle.crt',          },      }   } 
  • Ensure that the following line is at the top of the postgres.py file: 
from awx.main.utils import decrypt_value, get_encryption_key 
  • Replace the Password value

From: 

'PASSWORD': """YOUR_DB_PASSWORD""", 

To: 

'PASSWORD': decrypt_value(get_encryption_key('value'),'$encrypted$UTF8$AESCBC$Z0FBQUFBQmtCbjNxcE56VzZ3SmU3d2VvMDc1T1RQeGhnampxWEpzX2J3alRuMVZFMm9IQkZ1bEQ2RW9OREUwbXI0UG9XNmZWRU1TOTZzN2hyJUZ3I4LVczR2xaQlE9PQ=='), 
  • Where decrypt_value(get_encryption_key(‘value’) is the hash generated in the previous step 
  • The resulting file should look like the following:  
# Ansible Automation Platform controller database settings.   from awx.main.utils import decrypt_value, get_encryption_key   DATABASES = {      'default': {          'ATOMIC_REQUESTS': True,          'ENGINE': 'awx.main.db.profiled_pg',          'NAME': 'awx',          'USER': 'awx',          'PASSWORD': decrypt_value(get_encryption_key('value'),'$encrypted$UTF8$AESCBC$Z0FBQUFBQmtCbjNxcE56VzZ3SmU3d2VvMDc1T1RQeGhnampxWEpzX2J3alRuMVZFMm9IQkZ1bEQ2RW9OREUwbXI0UG9XNmZWRU1TOTZzN2hyJUZ3I4LVczR2xaQlE9PQ=='),          'HOST': 'db.example.net',          'PORT': '5432',          'OPTIONS': { 'sslmode': 'prefer',                       'sslrootcert': '/etc/pki/tls/certs/ca-bundle.crt',          },      }   } 
  • Start controller services on all nodes, using the following command:  
# automation-controller-service start 
  • Verify that the services started as expected:  
# automation-controller-service status 
  • Verify that you can connect to the UI and all the objects are visible

Rollback Procedure

  • Open the SSH session to all controller nodes (if the AAP has been configured in a cluster configuration) 
  • Elevate to the root  
  • Stop controller services on all nodes, using the following command: 
# automation-controller-service stop 
  • Copy the original postgres.py file from /root/backup to /etc/tower/conf.d/ directory on all controller nodes
cp /root/backup/postgres.py /etc/tower/conf.d/ 
  • Start controller services on all nodes, using the following command:
# automation-controller-service start 
  • Verify that the services started as expected: 
# automation-controller-service status 
  • Verify that you can connect to the UI and all the objects are visible 

Note: It is recommended to rollback the configuration before AAP upgrade, as the AAP installer will replace the file. Also note that this process works for AAP 2.x but it might not work in future.

CONCLUSION

To ensure the security of your Ansible Automation Platform Controllers, it’s crucial to encrypt the PostgreSQL database password. As outlined in the procedure above, this can be done by updating the configuration file and encrypting the password to reduce the risk of unauthorized access. Take action now and follow the steps to enhance the security of your system. Contact us for any further assistance.

RELATED ARTICLES

AWS RDS Disaster Recovery for AAP

Replacing Ansible Automation Private Automation Hub (PAH) Certificates

Ansible Disaster Recovery Guide AWS

Introduction to Ansible Builder

Join the Insentra Community with the Insentragram Newsletter

Hungry for more?

If you’re waiting for a sign, this is it.

We’re a certified amazing place to work, with an incredible team and fascinating projects – and we’re ready for you to join us! Go through our simple application process. Once you’re done, we will be in touch shortly!

New Zealand | Encrypting the password of the PostgreSQL database used by AAP Controllers

Who is Insentra?

Imagine a business which exists to help IT Partners & Vendors grow and thrive.

Insentra is a 100% channel business. This means we provide a range of Advisory, Professional and Managed IT services exclusively for and through our Partners.

Our #PartnerObsessed business model achieves powerful results for our Partners and their Clients with our crew’s deep expertise and specialised knowledge.

We love what we do and are driven by a relentless determination to deliver exceptional service excellence.

New Zealand | Encrypting the password of the PostgreSQL database used by AAP Controllers

Insentra ISO 27001:2013 Certification

SYDNEY, WEDNESDAY 20TH APRIL 2022 – We are proud to announce that Insentra has achieved the  ISO 27001 Certification.