000027335 - Connecting to or querying the database using pgSQL in RSA Authentication Manager 8.x

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support on Aug 14, 2019
Version 5Show Document
  • View in full screen mode

Article Content

Article Number000027335
Applies ToRSA Product Set: SecurID
RSA Product/Service Type: Authentication Manager
RSA Version/Condition: 8.x
O/S Version: SUSE
IssueThis article provides information on how to:
  • Connect to the Authentication Manager 8.x database using pgSQL;
  • Query the Authentication Manager 8.x database from command line and output to a file;
  • Run SQL statements in Authentication Manager 8.x; and
  • Run a report from the command line.

Connect to Authentication Manager 8.x Database to View or List Database Objects

Before making any changes to the database, make a back up of the database and store the backup in a secure location.

  1. Create a read-only database user.  In the example below, the users ocaadmin and rouser are used only for examples.  

cd /opt/rsa/am/utils
./rsautil manage-readonly-dbusers -a create -o ocadmin -u rouser -i <IP address where SSH is enabled>
Enter Operations Console (OC) password: <enter Operations Console admin user name>
Enter password for the read-only database user: <enter password for the read-only user>
Confirm password for the read-only database user: <confirm password for the read-only user>
Executing action: 'create'.
Trusted Root SSL CA certificate was copied in file '/opt/rsa/am/utils/RSAAMTrustedRootSSLCA.crt'.
'create' action complete.

  1. From the command line, connect to the database as the read-only user.  Query the database using a SELECT statement.  Note that the statement must be enclosed in single quotes and must end with a semicolon:

cd /opt/rsa/am/pgsql/bin
./psql -h localhost -p 7050 -d db -U rouser -c 'SELECT name FROM am_host;'
Password for user rouser: <enter password for the read-only database user>

Sample output is as follows:


  1. To output the results of the query above to a file, add the -o option. There will be no screen output when using this option.  The resulting am_agent_list.csv file will be written to /opt/rsa/am/pgsql/bin.

./psql -h localhost -p 7050 -d db -U rouser -o myfilename.csv -c 'SELECT name FROM am_host;'

  1. The SELECT statement also can be written in a file and run as in the example below:

cd /opt/rsa/am/pgsql/bin
./psql -h localhost -p 7050 -d db -U rouser -f  /path_to_file/mysqlquery.sql
Password for user rouser: <enter password for the read-only database user>

  1. Query the database using a SELECT statement from the psql command line:

cd /opt/rsa/am/pgsql/bin
./psql -h localhost -p 7050 -d db -U <read-only user>

db=> SELECT serial_number FROM rsa_rep.am_token;

Sample output:


  1. When done, use \q to exit the pgSQL database command line:

db=> \q

Note on the SSL certificate

When a read-only user account is created, the root CA SSL certificate file (RSAAMTrustedRootSSLCA.crt) is copied into /opt/rsa/am/utils. Transfer this SSL certificate file to the client machine from which you will be accessing the database, using a mechanism such as FTP or SCP for the file transfer. The SSL certificate must be set as a trusted root CA certificate in the client application you will use to connect to the database. See your SQL client utility documentation to set this as a trusted root CA certificate.
Legacy Article IDa61308