Enterprise Manager cannot authenticate with the Postgres database

Products

• APP PERF MANAGEMENT
• CA Application Performance Management Agent (APM / Wily / Introscope)
• CUSTOMER EXPERIENCE MANAGER
• INTROSCOPE

Issue/Introduction

• Tess-db-cfg.xml file contained the correct password for the admin user for the Postgres database.
• It was also not possible to connect to the database using pgAdmin utility using admin and correct password
• The postgres service user could connect to the database, either as configured in tess-db-cfg.xml or using the pgAdmin utility
• Reviewing the postgresql log in the /data/pg_log folder, you will just see these errors;

password authentication failed for user "admin"

Cause

When reviewing the details for the admin user in pgAdmin, an expiration date was set on the user, that was in the past, in this case January 1st 1970.

Logged into the database as the postgres user, this expiry could be seen:

1) pgAdmin

In your database connection, select Login Roles > admin

In the SQL pane, you may see something like this

CREATE ROLE admin LOGIN
  ENCRYPTED PASSWORD 'md57f5ae921aecd378486da929310909c8c'
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL '1970-01-01 00:00:00';

Note the VALID UNTIL restriction with a past date, this is causing the connection failure.

In Login Roles > admin, right-click on Properties and in the Definition tab, the Account expires value will be checked with a date.

<Please see attached file for image>

 

2) psql command line

Login - psql -u postgres -d cemdb

cemdb=# SELECT rolname,rolvaliduntil from pg_roles;
 rolname  |     rolvaliduntil
----------+------------------------
 postgres |
 admin    | 1970-01-01 00:00:00+01

Note that admin user has a date in the past in rolvaliduntil

Environment

Enterprise Manager 10.2 running on AIXPostgres DB running on Windows 2012

Resolution

To resolve this, we need to remove the restriction, effectively removing the admin account expiry

Either

a) pgAdmin

In Login Roles > admin, right-click and select Properties.

Move to the second tab called Definition

Uncheck the box next to Account expires and click OK

Or

b) psql command line

Run this statement:

cemdb=# ALTER ROLE admin VALID UNTIL 'infinity';
ALTER ROLE

You can then verify

cemdb=# SELECT rolname,rolvaliduntil from pg_roles;
 rolname  | rolvaliduntil
----------+---------------
 postgres |
 admin    | infinity
(2 rows)

Use of infinity for expiry is inspired by Postgresql documentation, referenced below.

 

3) You will need to restart the Enterprise Manager to be assured that the connection to the database can be restored

 

 

Additional Information

Link to PostgreSQL documentation about ALTER ROLE commands

https://www.postgresql.org/docs/9.2/static/sql-alterrole.html