Tuesday, March 22, 2022

Updating Centos Application related to security

While a Centos Linux server version is still supported, its application security updates is provided by the distro and its related repository. Application security updates are maintained by most large projects such as Apache HTTPD 2.4 and Supervisor.

Side note, there is a nice article on use of CentOS Stream at crunchtools.

Few things that can be done.

Identify the installed Centos.

uname -r

cat /etc/redhat-release

View latest security advisory CVEs.

yum updateinfo list cves

sudo dnf updateinfo list security

sudo dnf list-security

If above information doesn't show any applications, then there are no security related updates. General updates is viewed with

sudo dnf updateinfo list

Check update for HTTPD. As shown below, there is an available package for update, but no CVE is ragged..

dnf check-update httpd

dnf info httpd

In this next example, there is a CVE tagged to the update. 

dnf check-update supervisor

dnf info supervisor

Information on the update is available.
dnf updateinfo info FEDORA-EPEL-2021-1ad3a13e05

View installed notes on application updates.

rpm -q --changelog httpd

rpm -q --changelog supervisor

The said changes can then be updated

sudo dnf update supervisor

Wednesday, January 12, 2022

Howto redirect HTTP to HTTPS in Apache httpd

 Apache web server is an open source software (OSS) that is widely used on many sites even though there are a few newer web servers. 

HTTP request and response are transmitted over the network in plain text format. Anyone with a network sniffing tools or a man-in-the-middle (MitM) tool can easily intercept and read the information. Widely used internet search engine, Google began to use HTTPS as a search ranking signal in 2014. 

HTTPS request and response are transmitted using TLS or SSL, that encrypts the data travelling on the network. Those special tools are able to capture the data, but because it is encrypted, its of not much use to the average hackers and intruders. Using HTTPS provide advantages over HTTPS, such as;

  1. Data is encrypted, making it safer to transmit sensitive information over the network.
  2. Modern web browsers are better able to inform users if the site is safe in many aspects. Such as having a valid certificate or if data is coming from where its suppose to be instead of some unidentifiable source.
  3. Use of modern HTTP/2 protocol provide a better user experience, which includes improved site performance and security.
  4. Free wifi hotspots that tend to inject advertisements can be prevented.
  5. Web browsers can advice and prevent users from accessing data sensitive features such as geolocation, device motion and orientation if the site isn't HTTPS
Looking forward: 
  1. Legacy web browsers such as MS Internet Explorer do not have compatible SSL 2.0 and TLS 1.2. Which is why there is a need to disable its SSL 2.0 features when visiting HTTPS sites. (MS KB 2851628)
  2. Malware have started to ride on HTTPS encrypted features. Which means more advanced network monitoring tools are required to manage such malware.
In order to ensure web browsers are only using HTTPS, any request that is HTTP must be forwarded to a HTTP request. This is done with Apache httpd directive "Redirect".

E.g. where our host is harmonyshades.com.

Step 1: Edit Apache configuration file and include this. 

<VirtualHost *:80>
  ServerName harmonyshades.com
  Redirect / https://harmonyshades.com

Step 2: Restart wen server

systemctl restart httpd

Test on a web browser. HTTP pages will automatically load HTTPS pages.

Thursday, December 30, 2021

Edit Postgresql configuration without restart of database

Postgresql common configuration files are the postgresql.conf and pg_hba.conf. In many cases, its possible to edit without the need to restart the database. Here are the 2 options on  Centos Linux;

Use systemctl

# systemctl reload postgresql-11


# systemctl reload postgresql

Within Postgresql

Login and and access psql as admin or user postgres then run this sql.

SELECT pg_reload_conf();

Check Postgresql connections

Postgresql database provides 2 main configurations to limit the incoming connections. This is done in the file postgresql.conf

max_connections = 200                   # (change requires restart)
superuser_reserved_connections = 3      # (change requires restart)

Notes here refers to Postgresql version 11.

Following are common sql commands to monitor connections.

List number of connections

SELECT count(distinct(numbackends)) 
  FROM pg_stat_database; 

List connections by database

SELECT datname, numbackends 
  FROM pg_stat_database; 

    datname     | numbackends
 postgres      |           1
 template1     |           0
 template0     |           0
 shop01        |           0
 tutorial_php  |           0
 tutorial_linux|           0
 helpdesk      |          27
 telegram      |           6
(8 rows)

List the connections on a specific database

SELECT * FROM pg_stat_activity 
  WHERE datname='helpdesk';

List number of session that is active or idle.

SELECT state, count(*) FROM pg_stat_activity  
  WHERE pid <> pg_backend_pid() 

        state        | count
 active              |    55
 idle                |    38
 idle in transaction |     2
                     |     5
(4 rows)

These will provide data for planning and risk mitigations.

Wednesday, December 29, 2021

Howto clone existing GIT repository

There are many cases where remote git repositories provide a central location to store files such as source codes. The command 'clone' is used to retrieve a remote git repository. Depends on how a user is provided access to the server, by http, ssh or other methods. 

Here is how its done on Centos Linux, where it defaults to create a folder which is the same name as the repository.

cd /var/www

git clone ssh://nicholas@remoteserver:22/repo/tboxmy.git

Another approach is to create in our empty folder myproject

git clone ssh://nicholas@remoteserver:22/repo/tboxmy.git myproject

How to add user to the remote

git remote add newuser ssh://nicholas@remoteserver:22/repo/tboxmy.git

View remote users

git remote -v 

Wednesday, November 24, 2021

Adding standby node to Postgresql replication

 Postgresql database version 11 uses write ahead log (WAL) to ensure data integrity. It contains a log of all activity within the database, and helps with recovery of data. WAL records are saved to a WAL file. The insert position is a log sequence number (LSN) that is a byte offset into the logs, increasing with each new record.

Here, I am provided a Postgresql database that have been configured as a MASTER database. A user has been created on the Linux server and a role within the Postgresql called "replication". Now a new linux server have been provided for purpose of running a standby node, this has the same version of Postgresql database as in the MASTER server.

Installation of Standby

For simplicity the IP of MASTER will be, and for STANDBY will be

Following information is provided by the MASTER slave;

Linux username: replication

with password: password

Replication standby server is assigned name: replication

Folder of the installation: /var/lib/pgsql/11/data

Within MASTER, the STANDBY server IP has been added to its pg_hba.conf. 

Lets configure the STANBY server

Step 1: Login as postgres user

sudo su postgres

(enter your password)


Step 2: Replace local data with MASTER data

cd 11

mv data data.original

We will use the option -R to generate the default recovery.conf file.

pg_basebackup -h -U replication -D /var/lib/pgsql/11/data -P --password --slot replication -R

(enter password provided by MASTER)

chmod go-rw data

cd data

Step 3: Start Postgresql server

Ensure firewalld allows the database access.

Review contents of recovery.conf, standby_mode should be 'on'.

sudo systemctl start postgresql-11

Monitoring replication process

STANDBY server.

The latest WAL file, is stored in the folder data/pg_wal. Display current process of startup and which WAL file is being pocessed.

ps -eaf|grep startup

postgres  186454  186452  0 17:51 ?        00:00:02 postgres: startup   recovering 000000010000000C00000024

Display the LSN
ps -eaf|grep receiver

postgres  186460  186452  0 17:51 ?        00:00:06 postgres: walreceiver   streaming C/24C1DA10

Login to psql as postgres user,

SELECT pg_last_wal_receive_lsn();

(1 row)

SELECT pg_last_wal_replay_lsn();

(1 row)

MASTER server

Display the replication connection.
ps -eaf|grep postgres

It should display a line that contains
postgres: walsender replication streaming C/24C90AF8

login the psql,

sudo su postgres

SELECT pg_current_wal_lsn();

(1 row)

SELECT * FROM pg_stat_replication;

-[ RECORD 1 ]----+------------------------------
pid              | 2322396
usesysid         | 98654
usename          | replication
application_name | walreceiver
client_addr      |
client_hostname  |
client_port      | 49840
backend_start    | 2021-11-24 17:51:54.164144+08
backend_xmin     |
state            | streaming
sent_lsn         | C/24D88B18
write_lsn        | C/24D88B18
flush_lsn        | C/24D88B18
replay_lsn       | C/24D88B18
write_lag        | 00:00:00.000447
flush_lag        | 00:00:00.001416
replay_lag       | 00:00:00.001419
sync_priority    | 0
sync_state       | async

Determine if STANDBY is lagging.

On the STANDBY server, run these in psql.

SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn()
ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
END AS log_delay;

It should result in a value of 0 if everything is in sync.

Display the last last records written.
select pg_is_in_recovery(),pg_is_wal_replay_paused(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();

-[ RECORD 1 ]-----------------+------------------------------
pg_is_in_recovery             | t
pg_is_wal_replay_paused       | f
pg_last_wal_receive_lsn       | C/24DF6580
pg_last_wal_replay_lsn        | C/24DF6580
pg_last_xact_replay_timestamp | 2021-11-24 17:52:29.764273+08


Error Canceling statement due to conflict with recovery

This is due to standby server getting updates/deletes in the WAL stream that will invalidate data currently being accessed by a running query. Usually occurs when queries are time consuming.

Edit data/postgresql.conf to increase max_standby_streaming_delay, which allows the standby server to intentionally increase replication lag to allow queries to finish. Case where write of archives are taking a longer time than expected, then increase max_standby_archive_delay.

Note: Posting this ahead of time, for comments on understandability of information.

Tuesday, November 23, 2021

Howto: Find and kill a hanging query in Postgresql

 In normal case of a query to Postgresql database, it would take a very small amount of time to be processed. However, when query takes a long time to process then it will be a problem as resources such as connection, CPU and RAM will be locked to process that query.

Problem queries may lead to more queries in waiting until the database maximum connection is reached. Default Postgresql is set to maximum 100 connections. This will cause users to fail when trying to login to the application or trying to access the database. These 2 are common examples of when you will need to kill queries.

Lets see break this to 2 parts. 

Part 1, list max connection, and the current settings. Part 2, find the problem query, then to end it.

Part 1

Identify is the maximum connection to database has been reached.

Step 1: list the current maximum connection settings and where it is configured.

Login to postgresql terminal as admin (postgres) and use the commands.

SELECT name, setting, sourcefile FROM pg_settings WHERE name LIKE 'max_connections';

Step 2: List connections used

SELECT count(*) used FROM pg_stat_activity;

Compare values, to determine if this is an issue.

Part 2

Step 1: List query that is currently active

SELECT datid, pid, usename, client_addr, backend_start FROM pg_stat_activity WHERE state = 'active';

Step 2: Kill the query

SELECT pg_cancel_backend(PID);

Where PID is the value identified in Step 1 above.

Part 3

Identify long running queries can be done with a popular query mentioned in (https://wiki.postgresql.org/wiki/Lock_Monitoring)

SELECT a.datname,
         age(now(), a.query_start) AS "age",
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
ORDER BY a.query_start desc;

This concludes how to troubleshoot basic connection and hanging queries.

Monday, November 22, 2021

Add read only user in Postgresql

How to access to Postgresql where only reading of data is required?

Lets create a new user called "reader" and password as "password" who will be given access to the database mydatabase with READ only access. The following sign denotes the sql prompt, which you do not need to type.


The steps listed here are rather generic on PostgreSQL 11 onwards. There are other considerations for security and more complex database designs that you should look into in the future.

Step 1

Connect to the database as administrator.

psql -U postgres -H localhost

=# \c mydatabase

Step 2

Create the new user

=# CREATE USER reader WITH encrypted PASSWORD 'password';

=# GRANT CONNECT ON DATABASE mydatabase TO reader;


Determine the user exist with the following command

=# \du

Step 3

Verify this by login as that user. Additionally, the user access can be shown through the commands.

=# \l 
=# \l mydatabase

Connect to the selected database and list that user's privileges.

=# \c mydatabase

=# SELECT table_catalog, table_name from information_schema.role_table_grants WHERE grantee = "reader";

=# SELECT table_catalog, table_schema, table_name from information_schema.table_privileges WHERE grantee = "reader";

Managing user privileges

In the case where that user is no longer required, it is only prudent to remove that user from access to the database. It is common to maintain that user within the system, in case they need to assign the user to another database access.

=# REVOKE ALL ON DATABASE mydatabase FROM reader;

Adding user to an existing READ only role would allow faster management of users.

=# CREATE ROLE reader

=# CREATE USER dave IN ROLE reader


Document your steps for this intermediate exercise.

Create a new user with login access name "dave" and password "exercise". 

Create a database name "bookstore" with a table called "users". You are free to assign the field type to a sensible type. 


Fill this table "users" with 2 rows of data.

Assign user "dave" to be able to connect to this database and read contents of any table in this database.

Connect as user "dave" and verify if the user can only read data, without being able to update its data.

Blog Archive