How to fix "unable to execute query" JDBC exceptions when trying to view some CEM web pages which are due to incorrect postgres APM database table ownership.

Products

• CA Application Performance Management Agent (APM / Wily / Introscope)
• INTROSCOPE

Issue/Introduction

Description:

If ownership of some tables in the postgres APM database (cemdb) is incorrect it can cause this type of error when trying to view data on a CEM web page:
Internal Application Error: JDBC exception on Hibernate data access; nested exception is org.hibernate.exception.SQLGrammarException: could not executequery
Checking the DEBUG MOM log should show the root cause and often this can be permissions related to permissions for a table (relation) e.g.
Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for relation ts_defect_meta_values_20130218

NOTE: This type of error should not occur under normal operations where the ownership of all tables in the APM database (cemdb) should match the owner of the cemdb itself. By default that is the role "admin" which is the default username provided at cemdb install time.

If the username used in the tess-db-cfg.xml files is changed to be some other postgres role then new tables created for defects and stats. aggregation will be owned by the new role and that change can cause this type of problem.

Solution:

If the permissions error similar to the above is seen pgAdmin can be used to check the owner of the table in the cemdb.
If the owner is not the same as the owner of cemdb then it needs to be corrected to be so.

Assuming admin is the role that owns the cemdb, the ownership of all tables with this problem can be found using this SQL :
select tablename, tableowner from pg_tables where schemaname='public' and tableowner !='admin' order by tablename;

The ownership of all tables with this problem can be reset to the required ownership by defining and executing a function apm_reset_table_ownership().

1. To create the function using the pgAdmin utility, connect to the postgres server and navigate to the cemdb. Right mouse click and use the 'CREATE script" option. In the Query window that opens clear the SQL Editor tab contents, paste in the following SQL and execute the query to create the function:
   

   CREATE OR REPLACE FUNCTION apm_reset_table_ownership()RETURNS VOID AS $$   DECLARE        tbl_name    TEXT;   BEGIN       -- Change the table owner        FOR tbl_name IN select tablename from pg_tables where schemaname='public' and tableowner !='admin' LOOP       EXECUTE 'ALTER TABLE ' || quote_ident(tbl_name) || ' OWNER TO admin ';       END  LOOP;    END;   $$ LANGUAGE 'plpgsql' VOLATILE;

   
   A message such as "Query returned successfully with no result in xxx ms" should be returned.
   
   After execution, close the Query window and in the main window expand the cemdb tree to see -> Schemas -> public -> Functions to verify that the new function apm_reset_table_ownership() has been successfully created (a refresh/F5 may be required).
   
   
2. To execute the function:
   
   
   1. Stop all EMs in the cluster
   2. Backup the cemdb database.
   3. Run the function using this SQL:
      select apm_reset_table_ownership();
      
      There will be no data output from the function but you can check back on tables that had the ownership problem to verify that admin is now the owner (a refresh/F5 may be required).
      
      
   4. Ensure all EMs in the cluster have their file EM_HOME\config\tess-db-cfg.xml set to use the username of admin to connect to cemdb.
   5. Start the cluster

Environment

Release:
Component: APMCEM