000027335 - Connecting to or querying the RSA Authentication Manager 8.1 database using pgSQL

• Connect to the Authentication Manager 8.1 database using pgSQL;
• Query the Authentication Manager 8.1 database from command line and output to a file;
• Run SQL statements in Authentication Manager 8.1; and
• Run a report from command line.

Connect to Authentication Manager 8.1 Database to View or List Database Objects.

1. Create a read-only database user.  In the example below, the users ocaadmin and rouser are used only for examples.  See the Authentication Manager 8.1 Developer's Guide in the Authentication Manager 8.1 extras.zip, available for download from SecurCare Online, for more details. 
 

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.

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>

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;

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

db=> \q