How to run a SQL query for Authentication Manager 8.0 or 8.1 and write the output to a file for support
Originally Published: 2013-12-10
Article Number
Applies To
RSA Product/Service Type: Authentication Manager
RSA Version/Condition: 8.x
Issue
Resolution
To access the PostgreSQL shell
- Login as rsaadmin via SSH or console.
- Navigate to /opt/rsa/am/utils.
- Capture the database password string, entering the Operations Console administrator and password when prompted.
- 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
- 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>';
- 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.
- 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.
- To view the results, open the from_command.txt file in a text editor.
- 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.
- Create a file named listusers.sql.
- Put the command 'SELECT exuid, loginuid, identity_src_key FROM rsa_rep.ims_principal_data;' in the listusers.sql file and save it.
- Navigate to /opt/rsa/am/pgsql/bin.
- Run the database query from the command line, as follows.
- 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>
Related Articles
Connecting to or querying the database using pgSQL in RSA Authentication Manager 8.x Number of Views Does RSA support the installation of third party software or applications on the RSA Authentication Manager 8.X (virtual) … Number of Views Configure the RSA Authentication Agent API for Java on a supported platform with two network card interfaces (NIC) Number of Views Opening a Support Request with RSA Customer Support Number of Views RSA Authentication Manager virtual machine failed to boot server for JVM memory error Number of Views
Trending Articles
Artifacts to gather in RSA Identity Governance & Lifecycle How to install the jTDS JDBC driver on WildFly for use with Data Collections in RSA Identity Governance & Lifecycle Unable to attach a replica instance due to a configuration error when enabling replication for the RADIUS server for RSA A… Oracle 12c TEMP_UNDO_ENABLED parameter for managing GTT UNDO activity in RSA Identity Governance & Lifecycle RSA announces the availability of the RSA SecurID Hardware Appliance 230 based on the Dell PowerEdge R240 Server
Don't see what you're looking for?
