Accessing OpManager PGSQL Database

Accessing OpManager PGSQL Database



















INTRODUCTION


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 

                        For new OpManager versions, username is dbuser so the command becomes:
                        /opt/ManageEngine/OpManagerProbe/pgsql/bin/./psql -U dbuser -h 127.0.0.1 -p 13308  ProbeDB
                  

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/







    • Related Articles

    • Re-Index OpManager POSTGRes Database

      Query to check table size Log into the server GUI and go to the database lookup page "Support>DB Query". Execute the following commands and export the results. SELECT relname as "Table",pg_size_pretty(pg_total_relation_size(relid)) As "Total Size", ...
    • SQL Database query for OpManager

      SQL Database query for OpManager  OpManager allows searches for database information, while logged into the server there is a link at the top right to various links for help and searches. The image that looks like headphones is the link to support ...
    • Opmanager is attempting to contact Zoho Networks and SMTP

      Introduction A customer noted that the OpManager Probe server was attempting to send data to Manage Engine which was in violation of the Firewall rules that they had on their IP network. Settings that may need to be changed to stop automated data ...
    • OpManager Trap Processor Config

      OpManager Trap Processor Config Trap processor can be edited to display a number of labels and information based on the markup language and variables (VarBinds) https://pitstop.manageengine.com/portal/kb/articles/how-to-configure-trap-message Changes ...
    • Configuring configlets in OpManager NCM

      Manage Engine has written instruction on using and creating configlets. The link in this KB may be changed if Manage Engine update it, but for now it is a source for other NCM info as well ...