PostgreSQL database Password management

Follow the procedures given below to manage PostgreSQL database passwords.

Reset PostgreSQL Database connection password procedure using old password

For Connect:Direct Web Services v6.0.0.5 and above users running zLinux/AIX platform, follow the steps given below to reset the PostgreSQL password to a new value.
  1. Connect to the instance where PostgreSQL is installed.
  2. Login as a PostgreSQL user with root privileges that is, su – postgres
  3. Add the following line to the pg_hba.conf file to allow a user on the system to connect to local PostgreSQL database.
    host  all  postgres  127.0.0.1/32   trust
  4. Enter the current password used to connect to the PostgreSQL database.
    PGPASSWORD=<current password>
  5. Issue the following command to connect to the PostgreSQL database.
    ./psql -U postgres -p <PostgreSQL PORT>
  6. Issue the following command to set the password to a new value.
    ALTER USER <postgres_user> WITH PASSWORD <’new_password’>;
  7. Issue the following command to stop the Web Services.
    ./stopWebserviceZLinux.sh in /<Installation_Directory>/bin/
    ./stopWebserviceAIX.sh in /<Installation_Directory>/bin/
  8. Go to /<Installation_Directory>/mftws/BOOT-INF/classes and invoke the following command to apply password changes to Web services.
    java -jar ChangeDatabasePassword.jar 
  9. Launch Connect:Direct Web Services using the following utility:
    ./startWebservice.sh
    This utility is available in the /<Installation_Directory>/jre/bin/ directory.

Reset PostgreSQL Database connection password procedure using default password

For Connect:Direct Web Services users running Windows/UNIX platform, follow the steps given below to reset the PostgreSQL password to a new value.
  1. Navigate to the following directory.

    Windows

    Installation_Directory/PostgreSQL/bin
    UNIX
    Installation_Directory/PostgreSQL/pgsql/bin
  2. Execute the following command to connect to PostgreSQL database.
    psql.exe -U postgres -p <port_number>
    Enter Password as 'postgres'
  3. Execute the following command to change the PostgreSQL password that was entered when Web Services was installed.
    ALTER USER postgres WITH PASSWORD 'new_password';

Reset PostgreSQL database connection in case of forgotten password

  1. Connect to the instance where PostgreSQL is installed.
  2. Login as a PostgreSQL user with root privileges that is, su – postgres.
    1. Go to the /var/<FolderName_where_database_initialised > directory where PostgreSQL database is installed.
    2. Take a backup of the pg_hba.conf file that controls the client authentication. Create a backup file with name such as, pg_hbkp.conf file.
  3. Add the following line to the pg_hba.conf file to allow a user on the system to connect to local PostgreSQL database.
    host  all  postgres  127.0.0.1/32   trust
  4. Go to the bin directory and issue the following commands to restart PostgreSQL server:
    run ./pg_ctl -m fast -D /var/< FolderName_where_database_initialised> stop
    run ./pg_ctl -D /var/< FolderName_where_database_initialised> start  
  5. Issue the following command to connect to the PostgreSQL database server:
    ./psql -U postgres -p <PostgreSQL PORT>
  6. Issue the following command to change the password of the PostgreSQL server:
    ALTER USER postgres WITH PASSWORD 'very_secure_password';
  7. Restore the pg_hba.conf file, restart the PostgreSQL server, and connect to the PostgreSQL database server with new password.
  8. Invoke stopWebserviceZLinux.sh/stopWebserviceAIX.sh utility available in the /<Installation_Directory>/bin/ to stop the Web Services on the machine where it is currently installed.
  9. Go to /<Installation_Directory>/mftws/BOOT-INF/classes and invoke the following command to apply password changes to Web services.
    java -jar ChangeDatabasePassword.jar
  10. Launch Connect:Direct Web Services using the following utility:
    ./startWebservice.sh

    This utility is available in the /<Installation_Directory>/jre/bin/java directory.