Summary
Error
The following tool generates an error when executed on OBM DPS server:
/opt/HP/BSM/opr/support/EventStatisticsUtil.sh -a
Please ensure that the hpbsm user has superuser rights or is a member of the pg_stat_scan_table role
Please ensure that the pgstattuple extension is loaded: 'CREATE EXTENSION IF NOT EXISTS pgstattuple;'
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2545)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
at org.hibernate.loader.Loader.list(Loader.java:2271)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1842)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:157)
at com.hp.opr.cli.EventStatisticsUtil$LoadStatusGetter.getFragmentedIndexes(EventStatisticsUtil.java:1089)
at com.hp.opr.cli.EventStatisticsUtil$LoadStatusGetter.queryIndexOutput(EventStatisticsUtil.java:1122)
at com.hp.opr.cli.EventStatisticsUtil$LoadStatusGetter.printIndexCheck(EventStatisticsUtil.java:1220)
at com.hp.opr.cli.EventStatisticsUtil.main(EventStatisticsUtil.java:150)
Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for function pgstatindex
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2578)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2313)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:331)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:159)
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:109)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
at org.hibernate.loader.Loader.doQuery(Loader.java:802)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
at org.hibernate.loader.Loader.doList(Loader.java:2542)
... 10 more
SQL query select (select pc.relname from pg_class as pc where pc.oid = idx.indrelid) AS tablename, i.relname AS indexname, (pgstatindex(i.relname)).leaf_fragmentation AS fragmentationpercent FROM pg_index AS idx JOIN pg_class AS i ON i.oid = idx.indexrelid JOIN pg_namespace AS NS ON i.relnamespace = NS.OID WHERE NS.nspname = 'public' and NOT i.relname ~ 'DB_SCHEMA_CHANGELOG' order by FragmentationPercent DESC failed. Details: could not execute query
Table Name
Index Name
% Fragmented
Cause
According to the documentation, the following are the required database permissions that must be granted to the OBM user for all OBM databases (the Management, RTSM, and Event databases):
GRANT ALL ON DATABASE <mgmt db name> TO hpbsm;
GRANT ALL ON DATABASE <event db name> TO hpbsm;
GRANT ALL ON DATABASE <RTSM db name> TO hpbsm;
ALTER USER hpbsm WITH SUPERUSER;
Note: The superuser permissions are required for the registration of the uuid-ossp extension, which is used during database creation. The superuser permissions can be removed after the databases have been created.
The superuser permissions were removed after the creation of the database but no other permission was assigned to the user, so it didn't have access to the DB tables.
Fix
As it is mentoned in the error, the user shoud have superadmin privileges or pg_stat_scan_table role.
After adding pg_stat_scan_table role, the EventStatisticsUtil works fine.
More details about the permission from PostgrSQL documentation:
The pg_monitor, pg_read_all_settings, pg_read_all_stats and pg_stat_scan_tables roles are intended to allow administrators to easily configure a role for the purpose of monitoring the database server. They grant a set of common privileges allowing the role to read various useful configuration settings, statistics and other system information normally restricted to superusers.