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

[#1]

Article Number

000027335

Applies To

RSA Product Set: SecurID
RSA Product/Service Type: Authentication Manager
RSA Version/Condition: 8.x
O/S Version: SUSE

Issue

This 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.

2. 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

3. 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;'

4. 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>

5. 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

6. 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.