Replace HENGSHI metadb

During the use of HENGSHI SENSE services, the built-in metadb of HENGSHI can be replaced according to business needs. This article describes the process of replacing the HENGSHI metadb with a cloud service PostgreSQL.

Preparation

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

1. [Required] Configure HENGSHI Business Database Create users and databases, refer to the example below for the operation. In the example, the username and account name are both 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 the 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. [Optional] Configure HENGSHI Vector Database Create a pg_vector vector database account and grant permissions. Refer to the example below with the username vector.

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

5. [Required] Configure HENGSHI Business Database Permissions Use the super administrator account to authorize users. For Hengshi-sense version >= 4.4, the cloud service PostgreSQL database hengshi user requires CREATE/USAGE permissions on the pg_catalog (schema) of the hengshi and hengshi_sense_internal_storage databases, or the hengshi user must have super administrator privileges.

   • Check if 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 to 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 to check if the authorization operation is 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';

• The authorization successful query result is 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

• Connect to the hengshi and hengshi_sense_internal_storage databases in sequence using the hengshi user. Verify user permissions using the following SQL. If no error messages appear 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-related Configuration

Modify the following configuration items in the file conf/hengshi-sense-env.sh, and then restart HENGSHI SENSE. After restarting, the HENGSHI SENSE metadb replacement is successful.

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

export INTERNAL_STORAGE_DB_NAME="hengshi_sense_internal_storage"  # HENGSHI SENSE internal storage database

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

HS_VECTOR_HOST=xxx      # HENGSHI SENSE vector database host
HS_VECTOR_PORT=5432     # HENGSHI SENSE vector database port
HS_VECTOR_DB=vector     # HENGSHI SENSE vector database dbname
HS_VECTOR_USER=vector   # HENGSHI SENSE vector database user
HS_VECTOR_PASSWORD=vector20242024   # HENGSHI SENSE vector database password