OpManager tool relies on PostgreSQL (PGSQL) database for data storage. This document describes how to access the database to run customized reports

METHODS OF ACCESS

The PGSQL database can be accessed locally (or by using SSH session) from the PostgreSQL server using PGSQL shell, or remotely connecting to the server using DB client like SQL workbench

LOCAL ACCESS USING PGSQL SHELL 

To get the running PGSQL DB service port, type: ps a x | grep bin/postgres

To enter the PGSQL DB shell on server

/opt/ManageEngine/OpManagerProbe/pgsql/bin/./psql -U username -h hostname -p portnumber

E.g.

/opt/ManageEngine/OpManagerProbe/pgsql/bin/./psql -U postgres -h 127.0.0.1 -p 13308 

You can now use SQL commands. To exit shell type \q

REMOTE ACCESS USING PGSQL SHELL 

The PGSQL DB by default doesn’t respond to remote requests. So, the first step is to change this setting.

Also, it is recommended to create a Read-Only user to be used for remote access and assign it the appropriate privileges for security reasons

ALLOWING REMOTE ACCESS 

Change the access address of the DB to allow access from specific remote machine(s)

Execute the following command from the Linux shell to use vi editor to edit the file postgresql.conf

vi /opt/ManageEngine/OpManagerProbe/pgsql/data/postgresql.conf

Search for line containing “listen_addresses” and change line to 

listen_addresses = '*' allows listening for all addresses or 

listen_addresses = '0.0.0.0' allows listening for all IPv4 addresses or

listen_addresses = '::' allows listening for all IPv6 addresses

Remove the '#' at the beginning of the line to activate this setting

To save and quit, type :wq

Execute the following command from the Linux shell to use vi editor to edit the file pg_hba.conf

vi /opt/ManageEngine/OpManagerProbe/pgsql/data/pg_hba.conf

Add the following line to allow remote access for all users on all databases from all IP addresses with password:

host all all 0.0.0.0/0 md5

Or this line without password

host all all 0.0.0.0/0 trust

To save and quit, type :wq

After saving the file, DB process needs to be restarted using the following commands

cd /opt/ManageEngine/OpManagerProbe/bin/ 

./stopPgSQL.sh

./startPgSQL.sh 

wait for few seconds then press Ctrl+C

CREATING USER (ROLE) WITH READ-ONLY PRIVILEGE 

Using SSH connection to the server, enter DB PGSQL shell on server

/opt/ManageEngine/OpManagerProbe/pgsql/bin/./psql -U postgres -h127.0.0.1 -p13308 

To Create user “ro_usr” with password “PWD123”

CREATE USER RoleName WITH PASSWORD 'Password';

E.g. CREATE USER ro_usr WITH PASSWORD 'PWD123';

To list users 

SELECT usename FROM pg_user;

To list all DBs

\list or \l

To list all schemas

select nspname from pg_catalog.pg_namespace;

To list All tables in the “public” schema

\dt public.*

To grant “SELECT” privileges, please use the following commands 

GRANT CONNECT ON DATABASE postgres TO RO_USR;

GRANT USAGE ON SCHEMA public TO RO_USR;

GRANT SELECT ON All TABLES in SCHEMA public TO RO_USR;

To rollback (revoke privileges and delete user)

DROP OWNED BY RO_USR;

DROP USER RO_USR;

To exit shell type \q

USING SQL WORKBENCH TO CONNECT REMOTELY TO DB

Use the following link to download and setup the SQL workbench tool to connect to and access the PGSQL DB 

https://data36.com/install-sql-workbench-postgresql/install-sql-workbench-postgresql/

Create a new connection with URL “jdbc:postgresql://host:port/name_of_database”.

User name and password are to be set according to what is configured on the server

GENERAL SQL COMMANDS FOR PGSQL DB (LOCALLY OR REMOTELY) 

To list all tables

SELECT * FROM pg_catalog.pg_tables;

To list all fields from any table:

SELECT * FROM schema_name.table_name WHERE Condition;

To list all discovered MiMOMaX units:

SELECT displayname, dnsname, type

FROM managedobject join node 

on managedobject.name = node.name 

WHERE managedobject.type IN ('MIMOMAX-BRU-P','MIMOMAX-BRU-T','MIMOMAX-RRU-P','MIMOMAX-RRU-T');

REFERENCES

https://www.postgresql.org/docs/

https://pitstop.manageengine.com/portal/kb/opmanager

https://data36.com/