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.
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().
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;