Replace HENGSHI metadb

During the use of HENGSHI SENSE services, you can replace the built-in HENGSHI metadb according to business needs. This article explains the process of using cloud service PostgreSQL to replace the HENGSHI metadb.

Preparation Work

Before configuration, please complete the following preparations on the cloud service PostgreSQL.

It is recommended to configure the hengshi user as a high-privilege user.

1. [Required] Configure Hengshi Business Database
   Create users and databases. You can refer to the example below for operations. In the example, both the username and account name are hengshi.

   DO $body$ BEGIN IF NOT EXISTS (SELECT * FROM pg_catalog.pg_user WHERE  usename = 'hengshi') THEN CREATE ROLE hengshi CREATEDB CREATEROLE LOGIN PASSWORD 'hengshi202020'; END IF; END $body$;
   set role hengshi;
   create database hengshi;
   create database hengshi_sense_internal_storage;
   reset role;

2. [Required] Configure Hengshi Log Database
   Create a Hengshi log user, establish a Hengshi log database, and grant database permissions to the Hengshi log user. Refer to the example below for log user-related preparations.

   \c hengshi
   DO $body$ BEGIN IF NOT EXISTS (SELECT * FROM pg_catalog.pg_user WHERE usename = 'syslog') THEN CREATE ROLE syslog LOGIN PASSWORD 'syslog'; END IF; END $body$;
   GRANT ALL PRIVILEGES ON DATABASE hengshi to syslog;
   CREATE DATABASE syslog;
   GRANT ALL PRIVILEGES ON DATABASE syslog to syslog;

3. [Optional] Configure Read-Only Account
   Prepare a read-only account (this operation is configured after successfully starting the Hengshi service). Refer to the example below to set up a read-only account.

   \c hengshi
   DO $body$ BEGIN IF NOT EXISTS (SELECT * FROM pg_catalog.pg_user WHERE  usename = 'hsro') THEN CREATE ROLE hsro LOGIN PASSWORD 'hsro202020'; END IF; END $body$;
   GRANT USAGE ON SCHEMA public TO hsro;
   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO hsro;
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO hsro;
   GRANT USAGE ON SCHEMA quartz TO hsro;
   ALTER DEFAULT PRIVILEGES IN SCHEMA quartz GRANT SELECT ON TABLES TO hsro;
   GRANT SELECT ON ALL TABLES IN SCHEMA quartz TO hsro;

4. [Required] Configure Hengshi Business Database Permissions
   Use a super administrator account to perform user authorization operations.
   Hengshi-sense version >= 4.4 requires the cloud service PostgreSQL database hengshi user to have CREATE/USAGE permissions for the pg_catalog (schema) of the hengshi and hengshi_sense_internal_storage databases, or the hengshi user must have super administrator privileges.

   • Check whether the hengshi user has super administrator privileges

   select usename from pg_catalog.pg_user where usesuper = 't' and usename = 'hengshi';
   # If the query result is not empty, it means the hengshi user has super administrator privileges.

   Tip

   If the hengshi user has super administrator privileges, ignore the following steps.

   • Connect to the hengshi and hengshi_sense_internal_storage databases in sequence and perform authorization operations.

   \c hengshi
   GRANT ALL ON SCHEMA pg_catalog TO hengshi;
   \c hengshi_sense_internal_storage
   GRANT ALL ON SCHEMA pg_catalog TO hengshi;

   • Connect to the hengshi and hengshi_sense_internal_storage databases in sequence and check whether the authorization operation was successful.

   \c hengshi
   select nspname,pri_t,rolname from (SELECT nspname, (aclexplode(COALESCE(nspacl, acldefault('n', nspowner)))).grantee        AS grantee, (aclexplode(COALESCE(nspacl, acldefault('n', nspowner)))).privilege_type AS pri_t FROM pg_namespace WHERE nspname = 'pg_catalog') a left join pg_roles b on a.grantee=b.oid where rolname = 'HS_PG_USR';
   \c hengshi_sense_internal_storage
   select nspname,pri_t,rolname from (SELECT nspname, (aclexplode(COALESCE(nspacl, acldefault('n', nspowner)))).grantee        AS grantee, (aclexplode(COALESCE(nspacl, acldefault('n', nspowner)))).privilege_type AS pri_t FROM pg_namespace WHERE nspname = 'pg_catalog') a left join pg_roles b on a.grantee=b.oid where rolname = 'HS_PG_USR';

   • If the authorization is successful, the query result will be as follows, indicating that the hengshi user already has USAGE and CREATE permissions.

nspname	pri_t	rolname
pg_catalog	USAGE	HS_PG_USR
pg_catalog	CREATE	HS_PG_USR

• Use the hengshi user to connect to the hengshi and hengshi_sense_internal_storage databases in sequence. Use the following SQL to verify user permissions. If no error message appears during execution, the authorization operation is complete.

  CREATE OR REPLACE FUNCTION pg_catalog.myTestCreate(text)
   RETURNS numeric
   IMMUTABLE STRICT AS
   $
   BEGIN
   RETURN $1::numeric;
   EXCEPTION
       WHEN OTHERS THEN
           RETURN NULL;
   END
   $ LANGUAGE plpgsql;
  
   select myTestCreate('123');
  
   DROP FUNCTION pg_catalog.myTestCreate(text);

Modify Database Configuration

Modify the following configuration items in the file conf/hengshi-sense-env.sh, then restart HENGSHI SENSE. After restarting, the HENGSHI metadb will be successfully replaced.

HS_PG_HOST=xxx         # HENGSHI business database host
HS_PG_PORT=5432        # HENGSHI business database port
HS_PG_DB="hengshi"     # HENGSHI business database dbname
HS_PG_USR=hengshi      # HENGSHI business database user
HS_PG_PWD="hengshi"    # HENGSHI business database password

export INTERNAL_STORAGE_DB_NAME="hengshi_sense_internal_storage"  # HENGSHI internal storage database

HS_SYSLOG_HOST=xxxx    # HENGSHI log database host (usually the same as the business database)
HS_SYSLOG_PORT=5432    # HENGSHI log database port (usually the same as the business database)
HS_SYSLOG_DB=syslog    # HENGSHI log database dbname
HS_SYSLOG_USR=syslog   # HENGSHI log database user
HS_SYSLOG_PWD=syslog   # HENGSHI log database password

export HS_PG_IF_EXTERNAL=true  # Declare the use of an external PG database to replace the HENGSHI business database