Configuring the PostgreSQL database user accounts

About this task

Experience Portal uses the following PostgreSQL user accounts:

Default account name	Description
postgres	The database administrator can use this account to log in to the local Avaya Experience Portal database and perform database administration tasks. The password for this account is automatically generated. You cannot add other accounts of this type, delete this account, or change the account name. Important: Contact the Avaya Services representative to modify the local VoicePortal database as the database contains critical configuration information used to run the system.
report	This user account can only read those tables in the Experience Portal database that store report data. Speech application developers can use this account to log in to the database to create custom reports using any SQL-enabled report generation tool. You can have any number of accounts of this type with any account names.
reportwriter	This user account can only change the data in the tables that store report data in the Experience Portal database on the Auxiliary EPM server. You can have any number of accounts of this type with any account names. Important: Contact the Avaya Services representative to modify the tables that store report data in the local VoicePortal database.
vpcommon	This account is required if you plan to configure an Auxiliary EPM server. It allows the Auxiliary EPM servers limited access to the main Experience Portal database. You can delete this account or set the password for it, but you cannot add other accounts of this type or change the account name.

With the SetDbPassword.sh script, you can change all account passwords and add and delete all accounts except for postgres, which cannot be deleted.

Before you begin

If you have just installed the EPM software and are still logged into the EPM server, make sure that the environment variables are properly loaded as described in the Reloading the Avaya Experience Portal environment variables topic in the Implementing Avaya Experience Portal on multiple servers guide.

Procedure

Log in to Linux on the Primary EPM or Auxiliary EPM server.
- If you are an Avaya Services representative, and use Avaya Enterprise Linux, or if the Avaya Service accounts are installed on this server, log on to the local Linux console as root.
- Otherwise, log on remotely as a non-root user, and then change the user to root by entering the su - root command.
Enter the cd $AVAYA_HOME/Support/Security-Tools command.

$AVAYA_HOME is an environmental variable pointing to the name of the installation directory specified during the Experience Portal software installation.

Enter bash SetDbPassword.sh followed by the additional parameters to perform the required operation.

For details on the supported operations, see the following table. If you want to:

Option	Description
View a list of all Avaya Experience Portal PostgreSQL accounts	Enter the `bash SetDbPassword.sh list` command.
Change the password for a PostgreSQL user account	Enter the `bash SetDbPassword.sh update -u username` command, where: `username` is the name of the user account whose password you want to change Type the password you want to use for this account and press `Enter`. Note: If you change the password for the: postgres account, Avaya Experience Portal stops and then restarts the vpms service. vpcommon account on the Primary EPM server, you must also change the password on the Auxiliary EPM server as well.
Generate a random password for the PostgreSQL user account postgres	Enter the `bash SetDbPassword.sh reset_postgres` command.
Add a new report user with read only privileges	Enter the `bash SetDbPassword.sh add_report_r -u username` command, where: `username` is the PostgreSQL account name for the new report user Type the password you want to use for this account and press `Enter`.
Add a new report user with read/write privileges on an Auxiliary EPM server Note: You cannot add a report user with read/write privileges on the Primary EPM server.	Enter the `bash SetDbPassword.sh add_aux_report_w -u username` command, where: `username` is the PostgreSQL account name for the new report user Type the password you want to use for this account and press `Enter`.
Delete a report account from either EPM server or delete the vpcommon account from the Primary EPM server Note: You cannot delete the postgres user account.	Enter the `bash SetDbPassword.sh delete -u username` command, where `username` is either vpcommon or the report account name that you want to delete. For example, to delete the report account named RptReadWrite, you would enter: `bash SetDbPassword.sh delete -u RptReadWrite`
Add the vpcommon user account on the Primary EPM server so that an Auxiliary EPM server can access the database Note: You cannot add the vpcommon account to an Auxiliary EPM server.	Enter the `bash SetDbPassword.sh add_primary_vpcommon` command. Type the password you want to use for this account and press `Enter`.
Verify the vpcommon user on the local database that can connect to the 'auxname' (Auxiliary EPM) This option is applicable only on the Primary EPM.	Enter the `bash SetDbPassword.sh verify_aux_vpcommon -a auxname` command where: -a auxname is the configured name for the Auxiliary EPM.
Verify the Primary EPM’s vpcommon user. This option is applicable only on the Auxiliary EPM.	Enter the `bash SetDbPassword.sh verify_primary_vpcommon` command.
View the help for this command	Enter the `bash SetDbPassword.sh help` command.

After the script successfully completes the operation, the script prompts a message indicating the services to be restarted and asks if the user wants to proceed.

The following services will be restarted automatically:
-  postgresql
- vpms
- mmsserver
- avpSNMPAgentSvc
Do you wish to proceed? [Y/n]

Note:

The script will not list the service that is not impacted by changing the user password.

Type one of the following:
- Y to restart the services that are listed.
- n to cancel the restarting services.
  
  Note:
  If you cancel restating the services, you should manually restart the services for the changes to take effect.