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, 2016•Last modified by RSA Customer Support on Aug 14, 2019
Version 5Show Document
  • Comment • 0
  • 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.
Resolution

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:



name
------------------------------------
 2k8r2-lac72-1.2k8r2-vcloud.local
 am8p.vcloud.local


  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:



serial_number
---------------
 000112259786
 000112259787


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


db=> \q
Notes

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

Attachments

    Outcomes