How to manually delete a user record in PostgreSQL

Summary

You may find a circumstance where you have forgotten the password for the AX admin user, but do not have adequate licenses available to add another user through the aclconfig page. Manually deleting a user in the PostgreSQL database will allow you to add a new admin user and resolve the problem.

Environment

Analytics Exchange Server (AX)

Steps

1. Connect to the AX database using pgAdmin III, found by default in C:\ProgramData\Microsoft\Windows\Start Menu\Programs\PostgreSQL 9.3 (x86).

  Browse to the users table. See the screenshot below for the location.
  (Databases->AclAuditExchangeDB->Schemas->Public->Tables)
   

1. Click the grid icon from the tool bar to open the view of the data in the table.
2. Users can have one or more roles associated with them. This can help in determining which record you want to remove from the table.  For example, the users table below contains three user accounts:
       
3. Compare this to the content of the userrole table, where we can see that the SID that ends in 11899 has three roles. In particular, it has an entry for roleid of '1', which indicates it is an admin user.  If needed, you can check the roles table for a list of all available role types.
   
4. Once you've determined a user you'd like to remove, select the record in the users table, right click and then choose 'Delete' to delete the desired record.  This should automatically remove the associated rules from userrole as well, but you can check to be sure.
5. With a user slot now free, you can go to the aclconfig page to add a new AX admin user.

Note: 
      In PgAdmin 4 there is a trash icon that is used for deleting records on the toolbar