How to run a SQL query for Authentication Manager 8.0 or 8.1 and write the output to a file for support

[#1]

Article Number

000016923

Applies To

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

Issue

This article provides information on running SQL queries in the command line for Authentication Manager 8.0 and 8.1.

Resolution

To access the PostgreSQL shell

1. Login as rsaadmin via SSH or console.
2. Navigate to /opt/rsa/am/utils.
3. Capture the database password string, entering the Operations Console administrator and password when prompted.
4. Connect to the Postgres database.

login as: rsaadmin
Using keyboard-interactive authentication.
Password: <enter the operating system password>
Last login: Mon Oct 17 12:11:02 2016 from jumphost.vcloud.local
RSA Authentication Manager Installation Directory: /opt/rsa/am
rsaadmin@am81p:~> cd /opt/rsa/am/utils
rsaadmin@am81p:/opt/rsa/am/utils> ./rsautil manage-secrets -a get com.rsa.db.dba.password                                             
Please enter OC Administrator username: <enter the Operations Console admin name>
Please enter OC Administrator password: <enter the Operations Console admin password>
com.rsa.db.dba.password: rSKD5bGguLGNL9uGvFWnJoxIcHJah2
rsaadmin@am81p:/opt/rsa/am/utils> cd ../pgsql/bin
rsaadmin@am81p:/opt/rsa/am/pgsql/bin> ./psql -h localhost -p 7050 -d db -U rsa_dba
Password for user rsa_dba: <enter the com.rsa.db.dba.password string from above>
psql.bin (9.2.4)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

db=#

 

• Run SELECT queries from the db# pompt

1. After connecting to the database with the ./psql -h localhost -p 7050 -d db -U rsa_dba command, run a SELECT statement such as:

​SELECT exuid, loginuid, identity_src_key FROM rsa_rep.ims_principal_data WHERE loginuid = '<user_id>';

2.   Output will be as follows:

db=# SELECT exuid, loginuid, identity_src_key FROM rsa_rep.ims_principal_data WHERE loginuid = 'batgirl';
 exuid | loginuid |         identity_src_key
-------+----------+----------------------------------
       | batgirl  | bc23d5571e02a8c0188ce386ceceb107
(1 row)

 

• Run SELECT queries from rsautil and write output to a file

Alternatively, connect to Postgres with the –c option to specify psql is to execute the given command string and write output to a file named from_command.txt.

1. In this case, the connection string will be as follows:  ./psql -h localhost -p 7050 -d db -U rsa_dba -c 'SELECT exuid, loginuid, identity_src_key FROM rsa_rep.ims_principal_data;' -o /tmp/from_command.txt.
2. To view the results, open the from_command.txt file in a text editor.
3. Use WinSCP or FileZilla in SFTP mode to copy the file from the Authentication Manager server.

rsaadmin@am81p:/opt/rsa/am/pgsql/bin> ./psql -h localhost -p 7050 -d db -U rsa_dba -c 'SELECT exuid, loginuid, identity_src_key FROM rsa_rep.ims_principal_data;' -o /tmp/from_command.txt
Password for user rsa_dba: <enter the com.rsa.db.dba.password string from above>
rsaadmin@am81p:/opt/rsa/am/pgsql/bin> cat /tmp/from_command.txt
                      exuid                       |   loginuid    |                            identity_src_key                       
--------------------------------------------------+---------------+------------------------------------------------------------------------
                                                  | trustedapp    | Trusted-Application-User-ID
                                                  | @PROXYUSER@   | 3ea0de93db9110ac0022a056ce26e519
 \d0\7f\2c\88\55\7f\7b\45\86\8b\c3\f8\c0\b8\29\86 | Administrator | cn=administrator,cn=users,dc=2k8r2-vcloud,dc=local
                                                  | Tester        | e44634f11e02a8c01cfc97bd22810e05
                                                  | toolbartest   | a349e8e11e02a8c01b9337b66617857c
 \5b\2d\df\53\fd\22\b9\4c\89\b8\d7\af\d2\cf\3a\91 | rsa           | cn=rsa exchange,cn=users,dc=2k8r2-vcloud,dc=local
                                                  | support       | e3ce6e521e02a8c019ae43f3cdc74442
                                                  | sspbind       | ae7fa5671e02a8c0190a18d5060395f3
                                                  | amisbind      | c609f3d61e02a8c01af118800bab6d03
                                                  | batgirl       | bc23d5571e02a8c0188ce386ceceb107
                                                  | hdadmin       | 2487f56a1e02a8c019bbf4b2adb9867a
                                                  | rsaadmin      | 8afc03281e02a8c019d98c0ba37a25f1
                                                  | amistest      | 82c1c3c71e02a8c019e8209cf88ecc39
                                                  | flash         | f5cd613f1e02a8c01bbdea96db85a104
                                                  | admin         | 000000000000000000001000d0022000
                                                  | ironman       | 57b7789d1e02a8c01aa7f991f4e29841
                                                  | wonderwoman   | 6afa85b61e02a8c01ac55e493e91a743
                                                  | supergirl     | c08893061e02a8c01ae85d2b4c0b6b76
                                                  | arachne       | 27dafbc11e02a8c01affe7bdcb458c26
                                                  | blackwidow    | d693c0171e02a8c01b0474a160365f06
                                                  | darkstar      | 357f92be1e02a8c01af8be52bf681c8c
(21 rows)
rsaadmin@am81p:/opt/rsa/am/pgsql/bin>

Run SELECT queries from a file and write output to a file

The third option is to put  the SELECT statement in a file, then run the file with the –f option to read commands from the file defined in the command, rather than from standard input.

1. Create a file named listusers.sql.
2. Put the command 'SELECT exuid, loginuid, identity_src_key FROM rsa_rep.ims_principal_data;' in the listusers.sql file and save it.
3. Navigate to /opt/rsa/am/pgsql/bin.
4. Run the database query from the command line, as follows.
5. The output /tmp/from_file.txt has a list of all registered Authentication Manager users; that is those who have a token or a fixed passcode assigned, or have registered for Risk Based Authentication (RBA) or On-Demand Authentication (ODA or ODT) or with their security questions in the Self-Service Console. 

rsaadmin@am81p:/opt/rsa/am/pgsql/bin> ./psql -h localhost -p 7050 -d db -U rsa_dba -f /opt/rsa/am/utils/listusers.sql -o /tmp/from_file.txt
Password for user rsa_dba: <enter the com.rsa.db.dba.password string from above>
rsaadmin@am81p:/opt/rsa/am/pgsql/bin> cat /tmp/from_file.txt
   loginuid    |                      exuid                       |                            identity_src_key                       
---------------+--------------------------------------------------+------------------------------------------------------------------------
 trustedapp    |                                                  | Trusted-Application-User-ID
 @PROXYUSER@   |                                                  | 3ea0de93db9110ac0022a056ce26e519
 Administrator | \d0\7f\2c\88\55\7f\7b\45\86\8b\c3\f8\c0\b8\29\86 | cn=administrator,cn=users,dc=2k8r2-vcloud,dc=local
 Tester        |                                                  | e44634f11e02a8c01cfc97bd22810e05
 toolbartest   |                                                  | a349e8e11e02a8c01b9337b66617857c
 rsa           | \5b\2d\df\53\fd\22\b9\4c\89\b8\d7\af\d2\cf\3a\91 | cn=rsa exchange,cn=users,dc=2k8r2-vcloud,dc=local
 support       |                                                  | e3ce6e521e02a8c019ae43f3cdc74442
 sspbind       |                                                  | ae7fa5671e02a8c0190a18d5060395f3
 amisbind      |                                                  | c609f3d61e02a8c01af118800bab6d03
 batgirl       |                                                  | bc23d5571e02a8c0188ce386ceceb107
 hdadmin       |                                                  | 2487f56a1e02a8c019bbf4b2adb9867a
 rsaadmin      |                                                  | 8afc03281e02a8c019d98c0ba37a25f1
 amistest      |                                                  | 82c1c3c71e02a8c019e8209cf88ecc39
 flash         |                                                  | f5cd613f1e02a8c01bbdea96db85a104
 admin         |                                                  | 000000000000000000001000d0022000
 ironman       |                                                  | 57b7789d1e02a8c01aa7f991f4e29841
 wonderwoman   |                                                  | 6afa85b61e02a8c01ac55e493e91a743
 supergirl     |                                                  | c08893061e02a8c01ae85d2b4c0b6b76
 arachne       |                                                  | 27dafbc11e02a8c01affe7bdcb458c26
 blackwidow    |                                                  | d693c0171e02a8c01b0474a160365f06
 darkstar      |                                                  | 357f92be1e02a8c01af8be52bf681c8c
(21 rows)

rsaadmin@am81p:/opt/rsa/am/pgsql/bin>