How to Connect to the RSA Authentication Manager Database Using PostgreSQL (psql)
11 hours ago
Originally Published: 2013-04-23
Article Number
000065399
Applies To
RSA Product Set: SecurID
RSA Product/Service Type: Authentication Manager
RSA Version/Condition: 8.x
O/S Version: SUSE
Issue

Create a read-only PostgreSQL database user, connect to the RSA Authentication Manager database using the psql command-line client on the appliance, execute SELECT queries, and export query results to a file.

ℹ️ NOTE: This procedure applies only to the primary Authentication Manager server. Replica servers are not supported for this procedure.

⚠️ CAUTION: This procedure is intended for read-only database access only. Do not execute INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, or any other commands that modify the database. Unauthorized database changes can result in system instability, data corruption, and may affect RSA Support's ability to assist with the deployment.

Prerequisites

Before starting, ensure that all of the following requirements are met:

  • SSH is enabled on the Authentication Manager primary appliance.
  • You have the rsaadmin operating system account credentials (username and OS password).
  • You have the Operations Console (OC) administrator username and password.

  • No additional SQL client software is required. The psql client is pre-installed on the appliance at: 

    /opt/rsa/am/pgsql/bin/
    

    

    

    

    

    

    

    

    

    

    

    

    











 










Tasks
 


TaskToolKey Detail
Task 1: Create a Read-Only Database Userrsautil CLIRequired before any database connection
Task 2: Connect and Run an Inline Querypsql CLIResults displayed on screen
Task 3: Run a Query and Output to a Filepsql CLIResults saved to .csv at /opt/rsa/am/pgsql/bin
Task 4: Run a Query from a SQL Filepsql CLISQL stored in an external .sql file
Task 5: Run an Interactive psql Sessionpsql CLIEnter queries directly at the db=> prompt


 
Resolution
Task 1: Create a Read-Only Database User


⚠️ CAUTION: Before proceeding, back up the Authentication Manager database and store the backup in a secure location. Do not make any direct changes to the database.


 


Step 1: Navigate to the rsautil utilities directory:




cd /opt/rsa/am/utils


Step 2: Create the read-only database user:

./rsautil manage-readonly-dbusers -a create -o <oc-admin-username> -u <read-only-username> -i <IP address where SSH is enabled>


Step 3: Enter the Operations Console (OC) password when prompted:

Enter Operations Console (OC) password: <enter OC admin password>


Step 4: Set and confirm the password for the new read-only user:

Enter password for the read-only database user: <enter password>
Confirm password for the read-only database user: <confirm password>


Expected output:

Executing action: 'create'.
Trusted Root SSL CA certificate was copied in file '/opt/rsa/am/utils/RSAAMTrustedRootSSLCA.crt'.
'create' action complete.




ℹ️ NOTE: The root CA SSL certificate (RSAAMTrustedRootSSLCA.crt) has been copied to /opt/rsa/am/utils. You will need this file if connecting to the database from a remote client. See the Notes section for details.


 


Verification: Confirm you see 'create' action complete. in the output before proceeding to any of the tasks below. 


 




Task 2: Connect and Run an Inline Query


Step 1: Navigate to the psql binary directory:

cd /opt/rsa/am/pgsql/bin


Step 2: Connect to the database and run an inline SELECT query:

./psql -h localhost -p 7050 -d db -U <read-only-username> -c 'SELECT name FROM am_host;'


Step 3: Enter the read-only user password when prompted:




Password for user <read-only-username>: <enter password>


Expected output:

name
------------------------------------
am-server-1.example.com
am-server-2.example.com




 


ℹ️ NOTE: The SELECT statement must be enclosed in single quotes and must end with a semicolon.


Verification: Confirm query results are displayed on screen before proceeding.


 




Task 3: Run a Query and Output to a File


Step 1: Navigate to the psql binary directory:




cd /opt/rsa/am/pgsql/bin


 


Step 2: Run the query and redirect output to a CSV file using the -o option:

./psql -h localhost -p 7050 -d db -U <read-only-username> -o myfilename.csv -c 'SELECT name FROM am_host;'


 




ℹ️ NOTE: When using the -o option, there will be no output displayed on screen. The resulting file will be written to /opt/rsa/am/pgsql/bin.


 


Verification: Confirm the output file exists at /opt/rsa/am/pgsql/bin/myfilename.csv.


 






Task 4: Run a Query from a SQL File


 


Step 1: Navigate to the psql binary directory:

cd /opt/rsa/am/pgsql/bin


Step 2: Run the query using a pre-written SQL file with the -f option:




./psql -h localhost -p 7050 -d db -U <read-only-username> -f /path/to/mysqlquery.sql


Step 3: Enter the read-only user password when prompted:




Password for user <read-only-username>: <enter password>


 


Verification: Confirm query results are returned, or the output file is written, depending on the SQL file contents.


 






Task 5: Run an Interactive psql Session


Step 1: Navigate to the psql binary directory:




cd /opt/rsa/am/pgsql/bin


Step 2: Connect to the database interactively:




./psql -h localhost -p 7050 -d db -U <read-only-username>


Step 3: At the db=> prompt, run a SELECT query:

db=> SELECT serial_number FROM rsa_rep.am_token;


Expected output:


 




serial_number


---------------


 000112259786


 000112259787


 




Step 4: When finished, exit the psql session:


 




db=> \q


 


Verification: Confirm the terminal returns to the standard shell prompt after \q.


























































Notes


SSL Certificate for Remote Client Access: When a read-only user is created, the root CA SSL certificate file (RSAAMTrustedRootSSLCA.crt) is automatically copied to /opt/rsa/am/utils. If you are connecting to the database from a remote client machine, transfer this file using SCP or SFTP, then configure it as a trusted root CA certificate in your SQL client. Refer to your SQL client documentation for instructions on importing trusted root certificates.


 


Read-Only User Scope: The read-only database user created in Task 1 can only execute SELECT statements. INSERT, UPDATE, DELETE, and DROP operations are not permitted. Do not attempt to use administrative database credentials for queries.


 




Related Articles
Trending Articles
Don't see what you're looking for?