password authentication failed for user "admin"
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
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
Link to PostgreSQL documentation about ALTER ROLE commands
https://www.postgresql.org/docs/9.2/static/sql-alterrole.html