LVE Stats 2 and PostgreSQL DB Server Compatible Work Setup

Navigation:  LVE-Stats 2 > Configuration >

LVE Stats 2 and PostgreSQL DB Server Compatible Work Setup

Previous pageReturn to chapter overviewNext page

Note. Run all the commands below under root.

 

1. PostgreSQL Server Installation and Setup

 

1.1  PostgreSQL installation and initialization.

 

For control panels use proper documentation for installation on the links: сPanel, Plesk.

 

For non-panel CloudLinux run the following commands:

 

(CloudLinux 6)

 

yum install postgresql-server postgresql

service postgresql initdb

service postgresql start

chkconfig postgresql on

(CloudLinux 7)

 

yum install postgresql-server postgresql

postgresql-setup initdb

systemctl start postgresql

systemctl enable postgresql

 

 

1.2. Setup.

 

1. In /var/lib/pgsql/data/pg_hba.conf config file change user authentication mode. Add the following lines (place before all other authentication parameters):

 

# IPv4 local connections for lve-stats-2.x

host dblvestat all 127.0.0.1/32 password

# IPv6 local connections for lve-stats-2.x

host dblvestat all ::1/128 password

 

These lines enable user authentication by the password for IP4/IP6 connections. You can set other modes if needed.

 

3. Apply config changes by running:

 

service postgresql restart

 

 

2. DB for lve-stats-2.x - Creating and Setup

 

1. Run standard PostgreSQL psql administrative utility:

 

sudo -u postgres psql postgres 

 

(psql -w -U postgres for сPanel).

 

2. In utility run:

 

a.

 

CREATE DATABASE dblvestat;

 

creating server DB. Also, check Note below.

 

b.

 

CREATE USER lvestat WITH password 'passw';

 

creating a user for LVE Stats 2 server to work under. Also, check Note below.

 

c.

 

GRANT ALL privileges ON DATABASE dblvestat TO lvestat;

 

granting lvestat user all privileges for work with dblvestat DB.

 

d. \q - exit psql utility. (Alternatively Ctrl+d).

 

Note. DB name, username and their passwords above are given for an example - you can use any of your choices. Using old DB from LVE Stats version 1 is also acceptable as LVE Stats 2 uses different tables and the old information will not be corrupted.

 

 

3. Lve-stats-2.x Setup

 

Stop lve-stats2 server by running:

 

service lvestats stop

 

In server config file /etc/sysconfig/lvestats2 edit options for connecting to DB:

 

db_type = postgresql

connect_string=lvestat:[email protected]/dblvestat

If DB is going to be used as centralized for multiple hosts then collect_usernames parameter must be changed:

collect_usernames=true

 

Note that connect_string option value is of the format: user:[email protected]/database. Username, password and DB name must be the same as in Database Setup section above.

 

After making changes in configuration files, for DB primary initialization (creating tables, indexes, etc) run:

 

/usr/sbin/lve-create-db 

 

There is no need to create anything in the DB manually. When done, restart server by running:

 

service lvestats restart

 

 

4. Additional Security Settings

 

If you need to provide access to LVE Stats information utilities (lveinfo, lve-read-snapshot) for other users (or if CageFS is disabled), then in order to guarantee DB security the following steps are required:

 

a. Create a DB user with read-only permission:

 

CREATE USER lvestat_read WITH password 'passw';

GRANT CONNECT ON DATABASE dblvestat to lvestat_read;

\connect dblvestat;

GRANT SELECT ON lve_stats2_history, lve_stats2_history_gov, lve_stats2_history_x60, lve_stats2_incident, lve_stats2_servers, lve_stats2_snapshot, lve_stats2_user TO lvestat_read;

 

b. Assign root ownership and permission 600 to the main configuration file (/etc/sysconfig/lvestats2), so that it could be read only by LVE Stats 2 server and by utilities that run under root.

 

c. Copy /etc/sysconfig/lvestats2 to /etc/sysconfig/lvestats2.readonly, assign permission 644 to the new file, so that it could be read by any user but could be changed only by root.

 

d. In /etc/sysconfig/lvestats2.readonly file, in the line connect_string, specify DB user with read-only permission, created above.

 

These steps allow hiding main DB user username/password from other system users.

 

If there is no need in such access differentiation, then /etc/sysconfig/lvestats2 file access permission should be 644, so that it could be read by all users and could be changed only by root.

 

When done restart server by running:

 

service lvestats restart

 

 

5. Using Special Characters in Database Password

 

Since scheme://user:[email protected][:port]/database_name URI is used in connect_string config option, then usage of special characters in user DB password is not allowed . To use special symbols in the password, it must be converted to escape-sequence. You can convert a password to escape-sequence in a console as follows:

 

echo -n '[You_P@$$]:' | perl -MURI::Escape -ne 'print uri_escape($_)."\n"'

%5BYou_P%40%24%24%5D%3A

 

Or replace the symbols manually:

 

!    #    $    &    '    (    )    *    +    ,    /    :    ;    =    ?    @    [    ]

%21  %23  %24  %26  %27  %28  %29  %2A  %2B  %2C  %2F  %3A  %3B  %3D  %3F  %40  %5B  %5D

 

After that сonnect_string will look as follows:

 

сonnect_string=lvestats2:[email protected]/db_lvestats2