{"id":16722,"date":"2023-03-17T03:57:39","date_gmt":"2023-03-17T03:57:39","guid":{"rendered":"https:\/\/www.insentragroup.com\/au\/insights\/uncategorized\/encrypting-the-password-of-the-postgresql-database-used-by-aap-controllers\/"},"modified":"2024-12-13T02:22:54","modified_gmt":"2024-12-13T02:22:54","slug":"encrypting-the-password-of-the-postgresql-database-used-by-aap-controllers","status":"publish","type":"post","link":"https:\/\/www.insentragroup.com\/au\/insights\/geek-speak\/modern-workplace\/encrypting-the-password-of-the-postgresql-database-used-by-aap-controllers\/","title":{"rendered":"Encrypting the password of the PostgreSQL database used by AAP Controllers"},"content":{"rendered":"\n<p>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.&nbsp;&nbsp;<\/p>\n\n\n\n<p>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.&nbsp;&nbsp;<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Open the SSH session to all controller nodes (if the AAP has been configured in a cluster configuration)&nbsp;<\/li>\n\n\n\n<li>Elevate to the root<\/li>\n\n\n\n<li>Create a backup directory under \/root on all nodes<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code># mkdir -p \/root\/backup <\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Copy the original \/etc\/tower\/conf.d\/postgres.py file to \/root\/backup directory on <strong>all controller nodes<\/strong>&nbsp;<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code># cp \/etc\/tower\/conf.d\/postgres.py \/root\/backup <\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>As root, run the following command on one of the controller nodes:&nbsp;&nbsp;<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code># awx-manage shell_plus <\/code><\/pre>\n\n\n\n<p>You can expect output similar to the following:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ython 3.9.13 (main, Nov  9 2022, 13:16:24) \n\n&#91;GCC 8.5.0 20210514 (Red Hat 8.5.0-15)] on linux \n\nType \"help\", \"copyright\", \"credits\" or \"license\" for more information. \n\n(InteractiveConsole) \n\n&gt;&gt;&gt; <\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>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&nbsp;<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>&gt;&gt;&gt; from awx.main.utils import encrypt_value, get_encryption_key \n\n&gt;&gt;&gt; postgres_secret = encrypt_value('YOUR_DB_PASSWORD') \n\n&gt;&gt;&gt; print(postgres_secret) \n\n$encrypted$UTF8$AESCBC$Z0FBQUFBQmtCbjNxcE56VzZ3SmU3d2VvMDc1T1RQeGhnampxWEpzX2J3alRuMVZFMm9IQkZ1bEQ2RW9OREUwbXI0UG9XNmZWRU1TOTZzN2hyJUZ3I4LVczR2xaQlE9PQ== \n\n&gt;&gt;&gt; exit() <\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Save the encrypted value<\/li>\n\n\n\n<li>Stop controller services on all nodes, using the following command:&nbsp;&nbsp;<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code># automation-controller-service stop <\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>On each AAP controller node, navigate to \/etc\/tower\/conf.d<\/li>\n\n\n\n<li>Edit the postgres.py file and change it from the original format:&nbsp;<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>&gt;&gt;&gt; from awx.main.utils import encrypt_value, get_encryption_key \n\n&gt;&gt;&gt; postgres_secret = encrypt_value('YOUR_DB_PASSWORD') \n\n&gt;&gt;&gt; print(postgres_secret) \n\n$encrypted$UTF8$AESCBC$Z0FBQUFBQmtCbjNxcE56VzZ3SmU3d2VvMDc1T1RQeGhnampxWEpzX2J3alRuMVZFMm9IQkZ1bEQ2RW9OREUwbXI0UG9XNmZWRU1TOTZzN2hyJUZ3I4LVczR2xaQlE9PQ== \n\n&gt;&gt;&gt; exit() <\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Save the encrypted value&nbsp;<\/li>\n\n\n\n<li>Stop controller services on all nodes, using the following command:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code># automation-controller-service stop <\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>On each AAP controller node, navigate to \/etc\/tower\/conf.d&nbsp;&nbsp;<\/li>\n\n\n\n<li>Edit the postgres.py file and change it from the original format:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code># Ansible Automation Platform controller database settings. \n\n \n\nDATABASES = { \n\n   'default': { \n\n       'ATOMIC_REQUESTS': True, \n\n       'ENGINE': 'awx.main.db.profiled_pg', \n\n       'NAME': 'awx', \n\n       'USER': 'awx', \n\n       'PASSWORD': \"\"\"YOUR_DB_PASSWORD\"\"\", \n\n       'HOST': 'db.example.net', \n\n       'PORT': '5432', \n\n       'OPTIONS': { 'sslmode': 'prefer', \n\n                    'sslrootcert': '\/etc\/pki\/tls\/certs\/ca-bundle.crt', \n\n       }, \n\n   } \n\n} <\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Ensure that the following line is at the top of the postgres.py file:&nbsp;<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>from awx.main.utils import decrypt_value, get_encryption_key <\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Replace the Password value<\/li>\n<\/ul>\n\n\n\n<p><strong>From:&nbsp;<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>'PASSWORD': \"\"\"YOUR_DB_PASSWORD\"\"\", <\/code><\/pre>\n\n\n\n<p><strong>To:&nbsp;<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>'PASSWORD': decrypt_value(get_encryption_key('value'),'$encrypted$UTF8$AESCBC$Z0FBQUFBQmtCbjNxcE56VzZ3SmU3d2VvMDc1T1RQeGhnampxWEpzX2J3alRuMVZFMm9IQkZ1bEQ2RW9OREUwbXI0UG9XNmZWRU1TOTZzN2hyJUZ3I4LVczR2xaQlE9PQ=='), <\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Where decrypt_value(get_encryption_key(&#8216;value&#8217;) is the hash generated in the previous step&nbsp;<\/li>\n\n\n\n<li>The resulting file should look like the following:&nbsp;&nbsp;<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code># Ansible Automation Platform controller database settings. \n\nfrom awx.main.utils import decrypt_value, get_encryption_key \n\nDATABASES = { \n\n   'default': { \n\n       'ATOMIC_REQUESTS': True, \n\n       'ENGINE': 'awx.main.db.profiled_pg', \n\n       'NAME': 'awx', \n\n       'USER': 'awx', \n\n       'PASSWORD': decrypt_value(get_encryption_key('value'),'$encrypted$UTF8$AESCBC$Z0FBQUFBQmtCbjNxcE56VzZ3SmU3d2VvMDc1T1RQeGhnampxWEpzX2J3alRuMVZFMm9IQkZ1bEQ2RW9OREUwbXI0UG9XNmZWRU1TOTZzN2hyJUZ3I4LVczR2xaQlE9PQ=='), \n\n       'HOST': 'db.example.net', \n\n       'PORT': '5432', \n\n       'OPTIONS': { 'sslmode': 'prefer', \n\n                    'sslrootcert': '\/etc\/pki\/tls\/certs\/ca-bundle.crt', \n\n       }, \n\n   } \n\n} <\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Start controller services on all nodes, using the following command:&nbsp;&nbsp;<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code># automation-controller-service start <\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Verify that the services started as expected:&nbsp;&nbsp;<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code># automation-controller-service status <\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Verify that you can connect to the UI and all the objects are visible<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Rollback Procedure<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Open the SSH session to all controller nodes (if the AAP has been configured in a cluster configuration)&nbsp;<\/li>\n\n\n\n<li>Elevate to the root&nbsp;&nbsp;<\/li>\n\n\n\n<li>Stop controller services on all nodes, using the following command:&nbsp;<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code># automation-controller-service stop <\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Copy the original postgres.py file from \/root\/backup to \/etc\/tower\/conf.d\/ directory on <strong>all controller nodes<\/strong><\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>cp \/root\/backup\/postgres.py \/etc\/tower\/conf.d\/ <\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Start controller services on all nodes, using the following command:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code># automation-controller-service start <\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Verify that the services started as expected:&nbsp;<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code># automation-controller-service status <\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Verify that you can connect to the UI and all the objects are visible&nbsp;<\/li>\n<\/ul>\n\n\n\n<p>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.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">CONCLUSION<\/h2>\n\n\n\n<p>To ensure the security of your Ansible Automation Platform Controllers, it&#8217;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. <a href=\"https:\/\/www.insentragroup.com\/au\/contact\/\" target=\"_blank\" rel=\"noreferrer noopener\">Contact us<\/a> for any further assistance.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">RELATED ARTICLES<\/h2>\n\n\n\n<p><a href=\"https:\/\/www.insentragroup.com\/au\/insights\/geek-speak\/modern-workplace\/aws-rds-disaster-recovery-for-aap\/\" target=\"_blank\" rel=\"noreferrer noopener\">AWS RDS Disa<\/a><a href=\"https:\/\/www.insentragroup.com\/nz\/insights\/geek-speak\/modern-workplace\/aws-rds-disaster-recovery-for-aap\/\" target=\"_blank\" rel=\"noreferrer noopener\">s<\/a><a href=\"https:\/\/www.insentragroup.com\/au\/insights\/geek-speak\/modern-workplace\/aws-rds-disaster-recovery-for-aap\/\" target=\"_blank\" rel=\"noreferrer noopener\">ter Recovery for AAP<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/www.insentragroup.com\/au\/insights\/geek-speak\/modern-workplace\/replacing-ansible-automation-private-automation-hub-pah-certificates\/\" target=\"_blank\" rel=\"noreferrer noopener\">Replacing Ansible Automation Private Automation Hub (PAH) Certificates<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/www.insentragroup.com\/au\/insights\/geek-speak\/modern-workplace\/ansible-disaster-recovery-guide-aws\/\" target=\"_blank\" rel=\"noreferrer noopener\">Ansible Disaster Recovery Guide AWS<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/www.insentragroup.com\/au\/insights\/geek-speak\/modern-workplace\/introduction-to-ansible-builder\/\" target=\"_blank\" rel=\"noreferrer noopener\">Introduction to Ansible Builder<\/a><\/p>\n\n\n\n<style>\nbody .wp-block-code>code {\n    font-family: Menlo,Consolas,monaco,monospace;\n    color: #000;\n    padding: 30px 40px;\n    border: none;\n    border-radius: 4px;\n    background: #ddd;\n}\n<\/style>\n","protected":false},"excerpt":{"rendered":"<p>Learn how to encrypt the password of the PostgreSQL database used by AAP controllers to eliminate the risk of storing it in plain text format. Follow these steps to update the password on your AAP controller nodes. <\/p>\n","protected":false},"author":67,"featured_media":16723,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"content-type":"","footnotes":""},"categories":[19],"tags":[],"class_list":["post-16722","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-modern-workplace","entry"],"_links":{"self":[{"href":"https:\/\/www.insentragroup.com\/au\/wp-json\/wp\/v2\/posts\/16722","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.insentragroup.com\/au\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.insentragroup.com\/au\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.insentragroup.com\/au\/wp-json\/wp\/v2\/users\/67"}],"replies":[{"embeddable":true,"href":"https:\/\/www.insentragroup.com\/au\/wp-json\/wp\/v2\/comments?post=16722"}],"version-history":[{"count":1,"href":"https:\/\/www.insentragroup.com\/au\/wp-json\/wp\/v2\/posts\/16722\/revisions"}],"predecessor-version":[{"id":16724,"href":"https:\/\/www.insentragroup.com\/au\/wp-json\/wp\/v2\/posts\/16722\/revisions\/16724"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.insentragroup.com\/au\/wp-json\/wp\/v2\/media\/16723"}],"wp:attachment":[{"href":"https:\/\/www.insentragroup.com\/au\/wp-json\/wp\/v2\/media?parent=16722"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.insentragroup.com\/au\/wp-json\/wp\/v2\/categories?post=16722"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.insentragroup.com\/au\/wp-json\/wp\/v2\/tags?post=16722"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}