Error message "Temp table creation failed" when adding the IPDB Extractor as a Reporting Engine data source in RSA Security Analytics

[#1]

Article Number

000030701

Applies To

RSA Product Set: Security Analytics
RSA Product/Service Type: Security Analytics UI, Security Analytics Server, IPDB Extractor, Reporting Engine
RSA Version/Condition: 10.2.x, 10.3.x, 10.4.x
Platform: CentOS
O/S Version: EL6

Issue

When attempting to add the IPDB Extractor as a data source for the Reporting Engine in the Security Analytics UI, the error message below is displayed:

Temp table creation failed:
com.rsa.soc.datasource.DataSourceException:
java.sql.SQLException: Connections could not be acquired from the underlying database!

Image description

Listing the postgres databases on the Security Analytics server reveals that the nwtmpdb database is present, as shown below.

[root@SA-Server ~]# su - postgres
-bash-4.1$ psql -l
                                List of databases
   Name    |    Owner    | Encoding  | Collation | Ctype |   Access privileges
-----------+-------------+-----------+-----------+-------+-----------------------
 nwtmpdb   | nwipdbadptr | SQL_ASCII | C         | C     |
 postgres  | postgres    | SQL_ASCII | C         | C     |
 template0 | postgres    | SQL_ASCII | C         | C     | =c/postgres
                                                         : postgres=CTc/postgres
 template1 | postgres    | SQL_ASCII | C         | C     | =c/postgres
                                                         : postgres=CTc/postgres
(4 rows)

-bash-4.1$

Cause

This issue occurs because the nwipdbadptr user in postgres is not configured properly.

Resolution

Follow the steps below to alter the role for the nwipdbadptr user in order to resolve the issue.

1. Connect to the Security Analytics server appliance via SSH as the root user.
2. Switch to the postgres user with the following command:  su - postgres
3. Issue the following command to connect to the nwtmpdb database:  psql -d nwtmpdb

   -bash-4.1$ psql -d nwtmpdb
   psql (8.4.20)
   Type "help" for help.
   
   nwtmpdb=#

4. Issue the two queries below to alter the role for the nwipdbadptr and then type the \q command to exit the database.

   nwtmpdb=# ALTER ROLE "nwipdbadptr" WITH PASSWORD 'netwitness';
   ALTER ROLE
   nwtmpdb=# ALTER ROLE "nwipdbadptr" WITH LOGIN;
   ALTER ROLE
   nwtmpdb=# \q

5. Type the exit command to logout of the postgres user and return to the root user prompt.
6. Restart the postgresql service with the following command:  service postgresql restart

After making these changes, the IPDB Extractor will be able to be added as a Reporting Engine data source without any issue.
The password for the nwipdbadptr user should then be changed by following the instructions in the RSA Security Analytics User Guide.

If you are unsure of any of the steps above or experience any issues, contact RSA Support and quote this article number for further assistance.

Notes

If the nwtmpdb database does not exist when listing the postgres databases, contact RSA Support and quote this article number for assistance with manually creating the database.