Note. Run all the commands below under root.
1. PostgreSQL Server Installation and Setup
1.1 PostgreSQL installation and initialization.
For non-panel CloudLinux run the following commands:
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:
CREATE DATABASE dblvestat;
creating server DB. Also, check Note below.
CREATE USER lvestat WITH password 'passw';
creating a user for LVE Stats 2 server to work under. Also, check Note below.
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
If DB is going to be used as centralized for multiple hosts then collect_usernames parameter must be changed:
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:
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;
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 '[[email protected]$$]:' | perl -MURI::Escape -ne 'print uri_escape($_)."\n"'
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: