Usage of Practical grep commands examples useful in real world debugging in Linux

In our Daily debugging we need to analyze logs files of various products . Reading those log files are not an easy task , it requires special debugging skills which can only be gained through experience or by god’s grace . Now while debugging we might need to extract some of data or we need to play with a log file which can not be done by just reading , there is need for commands . 

There are many commands in linux which are used by debuggers like grep,awk,sed,wc,taskset,ps,sort,uniq,cut,xargs etc . . . 

In this blog we will see usage of Practical grep commands examples useful in real world debugging  in Linux . The examples which we will see in this blog are super basic but very useful in real life which a beginner should read to enhance the debugging skills . 

Let’s Go to the Practical Part 

  •  Grep the lines which contains some particular word
[root@localhost playground]# cat file1.log 
hello 
i am sahil 
i am software engineer 
Sahil is a software engineer
sahil is a software engineer

[root@localhost playground]# grep 'sahil' file1.log 
i am sahil 
sahil is a software engineer

  • Grep number of lines matched for a particualar word in a file
[root@localhost playground]# cat file1.log 
hello 
i am sahil 
i am software engineer 
Sahil is a software engineer
sahil is a software engineer

[root@localhost playground]# grep -c 'sahil' file1.log 
2

Another way :
[root@localhost playground]# grep 'sahil' file1.log  | wc -l
2

  • Grep all the lines in which contains some word in a file with case insensitive 
[root@localhost playground]# cat file1.log 
hello 
i am sahil 
i am software engineer 
Sahil is a software engineer
sahil is a software engineer

[root@localhost playground]# grep -i 'sahil' file1.log 
i am sahil 
Sahil is a software engineer
sahil is a software engineer
[root@localhost playground]# 

  • Grep the lines in which either of two words are present in a file
[root@localhost playground]# cat file1.log 
hello 
i am sahil 
i am software engineer 
Sahil is a software engineer
sahil is a software engineer

[root@localhost playground]# grep  'sahil\|software' file1.log 
i am sahil 
i am software engineer 
Sahil is a software engineer
sahil is a software engineer
[root@localhost playground]# 


  • Grep lines in which two words are present 
[root@localhost playground]# cat file1.log 
hello 
i am sahil 
i am software engineer 
Sahil is a software engineer
sahil is a software engineer

[root@localhost playground]# grep  'sahil' file1.log | grep 'software' 
sahil is a software engineer
[root@localhost playground]# ^C
[root@localhost playground]# 


  • Eliminate lines which contains some word  
[root@localhost playground]# cat file1.log
hello
i am sahil
i am software engineer
Sahil is a software engineer
sahil is a software engineer

[root@localhost playground]# grep -v 'sahil' file1.log
hello
i am software engineer
Sahil is a software engineer

Eliminate case insensitively 
[root@localhost playground]# grep -iv 'sahil' file1.log
hello
i am software engineer
[root@localhost playground]#

  • Matching the lines that start with a string
[root@localhost playground]# cat file1.log 
hello 
i am sahil 
i am software engineer 
Sahil is a software engineer
sahil is a software engineer

[root@localhost playground]# grep  '^sahil' file1.log 
sahil is a software engineer


  • Matching the lines that end with a string
[root@localhost playground]# cat file1.log 
hello 
i am sahil 
i am software engineer
Sahil is a software engineer
sahil is a software engineer

[root@localhost playground]# grep  'engineer$' file1.log 
i am software engineer
Sahil is a software engineer
sahil is a software engineer
[root@localhost playground]#

  • Getting n number of lines after each match 
[root@localhost playground]# cat file1.log 
hello 
i am sahil 
i am software engineer
Sahil is a software engineer
sahil is a software engineer
[root@localhost playground]# 

[root@localhost playground]# grep  'hello' file1.log 
hello 

[root@localhost playground]# grep -A 1  'hello' file1.log 
hello 
i am sahil 

[root@localhost playground]# grep -A 2  'hello' file1.log 
hello 
i am sahil 
i am software engineer


  • Geeting n number of lines before each match 
[root@localhost playground]# cat file1.log 
hello 
i am sahil 
i am software engineer
Sahil is a software engineer
sahil is a software engineer
[root@localhost playground]# grep  'i am sahil' file1.log 
i am sahil 
[root@localhost playground]# grep  -B 1 'i am sahil' file1.log 
hello 
i am sahil 
[root@localhost playground]# grep  -B 2 'i am sahil' file1.log 
hello 
i am sahil 
[root@localhost playground]# 


  • Grep n lines after and m lines before every match
[root@localhost playground]# cat file1.log 
hello 
i am sahil 
i am software engineer
Sahil is a software engineer
sahil is a software engineer
[root@localhost playground]# grep -A 2  -B 1 'i am sahil' file1.log 
hello 
i am sahil 
i am software engineer
Sahil is a software engineer
[root@localhost playground]# 

  • Grep some word in more than one file in current directory  
[root@localhost playground]# cat file1.log 
hello 
i am sahil 
i am software engineer
Sahil is a software engineer
sahil is a software engineer

[root@localhost playground]# cat file2.log 
hello 
i am sahil  
i am tech blogger
Sahil is a tech blogger
sahil is a tech blogger

[root@localhost playground]# grep  'sahil' file1.log file2.log 
file1.log:i am sahil 
file1.log:sahil is a software engineer
file2.log:i am sahil  
file2.log:sahil is a tech blogger

  • Grep some word in all files in current directory
[root@localhost playground]# grep  'sahil' *
file1.log:i am sahil 
file1.log:sahil is a software engineer
file2.log:i am sahil  
file2.log:sahil is a tech blogger
[root@localhost playground]# 

  • Check how much lines matched in each file   
[root@localhost playground]# grep -c  'sahil' *
file1.log:2
file2.log:2
file.log:0

  • Grep using regular expression —
    • Suppose the content of files are as follows
[root@localhost playground]# cat file3.log 
time taken by api is 1211 ms 
time taken by api is 2000 ms 
time taken by api is 3000 ms 
time taken by api is 4000 ms 
time taken by api is 50000 ms 
time taken by api is 123 ms 
time taken by api is 213 ms 
time taken by api is 456 ms 
time taken by api is 1000 ms 

Now suppose we want to grep all the lines in which time taken by any api is more than 1 second or more than 1000 ms , it means it should have minimum 4 digit number .

Now grep command for this will be as follows :

[root@localhost playground]# grep -P '[0-9]{4} ms' file3.log
time taken by api is 1211 ms 
time taken by api is 2000 ms 
time taken by api is 3000 ms 
time taken by api is 4000 ms 
time taken by api is 50000 ms 
time taken by api is 1000 ms 

If want to get 5 digit number 

[root@localhost playground]# grep -P '[0-9]{5} ms' file3.log
time taken by api is 50000 ms 

  • Recursively grep in a directory  and sub directoies
[root@localhost playground]# grep -R 'sahil' .
./dir1/file.log:i am sahil 
./dir1/file.log:sahil is a software engineer
./file1.log:i am sahil 
./file1.log:sahil is a software engineer
./file2.log:i am sahil  
./file2.log:sahil is a tech blogger
[root@localhost playground]#

All above are basic use cases of grep . One can mix all the command options of grep to achieve the complex use cases and also one can also mix different grep commands using pipe operator to achieve complex use cases .

In future blogs i will explain some complex use cases and example how to achieve that using linux commands which can ease logs debugging .

Stay Tuned . . .

Vulnerability Updates | Week of June 21, 2021

The CISA Vulnerability Bulletin provides a summary of new vulnerabilities that have been recorded by the National Institute of Standards and Technology (NIST) National Vulnerability Database (NVD) in the past week. NVD is sponsored by CISA. In some cases, the vulnerabilities in the bulletin may not yet have assigned CVSS scores. Please visit NVD for updated vulnerability entries, which include CVSS scores once they are available.

Vulnerabilities are based on the Common Vulnerabilities and Exposures (CVE) vulnerability naming standard and are organized according to severity, determined by the Common Vulnerability Scoring System (CVSS) standard. The division of high, medium, and low severities correspond to the following scores:

  • High: vulnerabilities with a CVSS base score of 7.0–10.0
  • Medium: vulnerabilities with a CVSS base score of 4.0–6.9
  • Low: vulnerabilities with a CVSS base score of 0.0–3.9

Entries may include additional information provided by organizations and efforts sponsored by CISA. This information may include identifying information, values, definitions, and related links. Patch information is provided when available. Please note that some of the information in the bulletin is compiled from external, open-source reports and is not a direct result of CISA analysis.

In this Blog , i am writing about High vulnerabilities only and some of Medium and Low if they it feels important to me .

For list of all vulnerabilities you can check CISA Bulletin .

High Vulnerabilities

Primary
Vendor — Product
DescriptionPublishedCVSS ScoreSource & Patch Info
apache — nuttxApache Nuttx Versions prior to 10.1.0 are vulnerable to integer wrap-around in functions malloc, realloc and memalign. This improper memory assignment can lead to arbitrary memory allocation, resulting in unexpected behavior such as a crash or a remote code injection/execution.2021-06-217.5CVE-2021-26461
CONFIRM
autoptimize — autoptimizeThe Autoptimize WordPress plugin before 2.7.8 attempts to delete malicious files (such as .php) form the uploaded archive via the “Import Settings” feature, after its extraction. However, the extracted folders are not checked and it is possible to upload a zip which contained a directory with PHP file in it and then it is not removed from the disk. It is a bypass of CVE-2020-24948 which allows sending a PHP file via the “Import Settings” functionality to achieve Remote Code Execution.2021-06-217.5CVE-2021-24376
CONFIRM
ayecode — location_managerIn the Location Manager WordPress plugin before 2.1.0.10, the AJAX action gd_popular_location_list did not properly sanitise or validate some of its POST parameters, which are then used in a SQL statement, leading to unauthenticated SQL Injection issues.2021-06-217.5CVE-2021-24361
MISC
CONFIRM
cleo — lexicomAn issue was discovered in Cleo LexiCom 5.5.0.0. Within the AS2 message, the sender can specify a filename. This filename can include path-traversal characters, allowing the file to be written to an arbitrary location on disk.2021-06-187.5CVE-2021-33576
MISC
MISC
contiki-ng — contiki-ngContiki-NG is an open-source, cross-platform operating system for internet of things devices. A buffer overflow vulnerability exists in Contiki-NG versions prior to 4.6. After establishing a TCP socket using the tcp-socket library, it is possible for the remote end to send a packet with a data offset that is unvalidated. The problem has been patched in Contiki-NG 4.6. Users can apply the patch for this vulnerability out-of-band as a workaround.2021-06-187.5CVE-2021-21281
MISC
CONFIRM
contiki-ng — contiki-ngContiki-NG is an open-source, cross-platform operating system for internet of things devices. It is possible to cause an out-of-bounds write in versions of Contiki-NG prior to 4.6 when transmitting a 6LoWPAN packet with a chain of extension headers. Unfortunately, the written header is not checked to be within the available space, thereby making it possible to write outside the buffer. The problem has been patched in Contiki-NG 4.6. Users can apply the patch for this vulnerability out-of-band as a workaround.2021-06-187.5CVE-2021-21280
MISC
CONFIRM
contiki-ng — contiki-ngContiki-NG is an open-source, cross-platform operating system for internet of things devices. In verions prior to 4.6, an attacker can perform a denial-of-service attack by triggering an infinite loop in the processing of IPv6 neighbor solicitation (NS) messages. This type of attack can effectively shut down the operation of the system because of the cooperative scheduling used for the main parts of Contiki-NG and its communication stack. The problem has been patched in Contiki-NG 4.6. Users can apply the patch for this vulnerability out-of-band as a workaround.2021-06-187.8CVE-2021-21279
CONFIRM
contiki-ng — contiki-ngContiki-NG is an open-source, cross-platform operating system for internet of things devices. In versions prior to 4.5, buffer overflow can be triggered by an input packet when using either of Contiki-NG’s two RPL implementations in source-routing mode. The problem has been patched in Contiki-NG 4.5. Users can apply the patch for this vulnerability out-of-band as a workaround.2021-06-187.5CVE-2021-21282
MISC
CONFIRM
google — androidIn updateDrawable of StatusBarIconView.java, there is a possible permission bypass due to an uncaught exception. This could lead to local escalation of privilege by running foreground services without notifying the user, with User execution privileges needed. User interaction is not needed for exploitation.Product: AndroidVersions: Android-10 Android-11 Android-8.1 Android-9Android ID: A-1692557972021-06-217.2CVE-2021-0478
MISC
google — androidIn handle_rc_metamsg_cmd of btif_rc.cc, there is a possible out of bounds write due to a missing bounds check. This could lead to remote code execution over Bluetooth with no additional execution privileges needed. User interaction is not needed for exploitation.Product: AndroidVersions: Android-11 Android-8.1 Android-9 Android-10Android ID: A-1818600422021-06-218.3CVE-2021-0507
MISC
google — androidIn the Settings app, there is a possible way to disable an always-on VPN due to a missing permission check. This could lead to local escalation of privilege with no additional execution privileges needed. User interaction is not needed for exploitation.Product: AndroidVersions: Android-11Android ID: A-1799750482021-06-217.2CVE-2021-0505
MISC
google — androidIn p2p_process_prov_disc_req of p2p_pd.c, there is a possible out of bounds read and write due to a use after free. This could lead to remote escalation of privilege with no additional execution privileges needed. User interaction is not needed for exploitation.Product: AndroidVersions: Android-11 Android-8.1 Android-9 Android-10Android ID: A-1816604482021-06-217.5CVE-2021-0516
MISC
greenbone — greenbone_security_assistantGreenbone Security Assistant (GSA) before 7.0.3 and Greenbone OS (GOS) before 5.0.0 allow Host Header Injection.2021-06-217.5CVE-2018-25016
MISC
MISC
jenkins — generic_webhook_triggerJenkins Generic Webhook Trigger Plugin 1.72 and earlier does not configure its XML parser to prevent XML external entity (XXE) attacks.2021-06-187.5CVE-2021-21669
CONFIRM
MLIST
joomla — joomla\!Joomla! Core is prone to a security bypass vulnerability. Exploiting this issue may allow attackers to perform otherwise restricted actions and subsequently retrieve password reset tokens from the database through an already existing SQL injection vector. Joomla! Core versions 1.5.x ranging from 1.5.0 and up to and including 1.5.15 are vulnerable.2021-06-217.5CVE-2010-1435
MISC
MISC
joomla — joomla\!Joomla! Core is prone to a vulnerability that lets attackers upload arbitrary files because the application fails to properly verify user-supplied input. An attacker can exploit this vulnerability to upload arbitrary code and run it in the context of the webserver process. This may facilitate unauthorized access or privilege escalation; other attacks are also possible. Joomla! Core versions 1.5.x ranging from 1.5.0 and up to and including 1.5.15 are vulnerable.2021-06-217.5CVE-2010-1433
MISC
MISC
primion-digitek — secure_8Secure 8 (Evalos) does not validate user input data correctly, allowing a remote attacker to perform a Blind SQL Injection. An attacker could exploit this vulnerability in order to extract information of users and administrator accounts stored in the database.2021-06-187.5CVE-2021-3604
CONFIRM
CONFIRM
radykal — fancy_product_designerThe Fancy Product Designer WordPress plugin before 4.6.9 allows unauthenticated attackers to upload arbitrary files, resulting in remote code execution.2021-06-217.5CVE-2021-24370
MISC
CONFIRM
serenityos — serenityosSerenityOS before commit 3844e8569689dd476064a0759d704bc64fb3ca2c contains a directory traversal vulnerability in tar/unzip that may lead to command execution or privilege escalation.2021-06-187.5CVE-2021-31272
MISC
MISC
MISC
CONFIRM
textpattern — textpatternTextpattern 4.7.3 contains an aribtrary file load via the file_insert function in include/txp_file.php.2021-06-217.5CVE-2020-19510
MISC
txjia — imcatSQL Injection vulnerability in imcat v5.2 via the fm[auser] parameters in coms/add_coms.php.2021-06-237.5CVE-2020-20392
MISC
white_shark_systems_project — white_shark_systemsWhite Shark System (WSS) 1.3.2 is vulnerable to unauthorized access via user_edit_password.php, remote attackers can modify the password of any user.2021-06-217.5CVE-2020-20466
MISC
white_shark_systems_project — white_shark_systemsWhite Shark System (WSS) 1.3.2 has an unauthorized access vulnerability in default_user_edit.php, remote attackers can exploit this vulnerability to escalate to admin privileges.2021-06-219CVE-2020-20471
MISC

For the complete list Please visit https://us-cert.cisa.gov/ncas/bulletins/sb21-179

Please subscribe hello-worlds.in for more updates .

Bulk Updation/Insertion of Database Tables in Java using Hibernate – Optimized Way

Hibernate is the most popular orm framework used to interact with databases in java . In this article  we will see what are the various ways using which bulk selection and updation in any table can be done and what is the most effective way when using the hibernate framework in java . 

I  experimented with three ways which are as follows : 

  • Using Hibernate’s Query.list() method.
  • Using ScrollableResults with FORWARD_ONLY scroll mode.
  • Using ScrollableResults with FORWARD_ONLY scroll mode in a StatelessSession.

To decide which one gives best performance for our use case, following tests i performed using the above three ways listed.

  • Select and update 1000 rows.

Let’s see the Code and results by applying above three ways to the operation stated above one by one. 

Using Hibernate’s Query.list() method.

Code Executed : 

   List rows;
        Session session = getSession();
        Transaction transaction = session.beginTransaction();
        try {
            Query query = session.createQuery("FROM PersonEntity WHERE id > :maxId ORDER BY id").setParameter("maxId",
                    MAX_ID_VALUE);
            query.setMaxResults(1000);
            rows = query.list();
            int count = 0;
            for (Object row : rows) {
                PersonEntity personEntity = (PersonEntity) row;
                personEntity.setName(randomAlphaNumeric(30));
                session.saveOrUpdate(personEntity);
                //Always flush and clear the session after updating 50(jdbc_batch_size specified in hibernate.properties) rows
                if (++count % 50 == 0) {
                    session.flush();
                    session.clear();
                }
            }
        } finally {
            if (session != null && session.isOpen()) {
                transaction.commit();
                session.close();
            }
        }

Tests Results : 

  • Time taken:- 360s to 400s
  • Heap Pattern:- gradually increased from 13m to 51m(from jconsole). 

Using ScrollableResults with FORWARD_ONLY scroll mode.

With this we are expecting that it should consume less memory that the 1st approach . Let’s see the results 

Code Executed : 

Session session = getSession();
        Transaction transaction = session.beginTransaction();
        ScrollableResults scrollableResults = session
                .createQuery("FROM PersonEntity WHERE id > " + MAX_ID_VALUE + " ORDER BY id")
                .setMaxResults(1000).scroll(ScrollMode.FORWARD_ONLY);
        int count = 0;
        try {
            while (scrollableResults.next()) {
                PersonEntity personEntity = (PersonEntity) scrollableResults.get(0);
                personEntity.setName(randomAlphaNumeric(30));
                session.saveOrUpdate(personEntity);
                if (++count % 50 == 0) {
                    session.flush();
                    session.clear();
                }
            }
        } finally {
            if (session != null && session.isOpen()) {
                transaction.commit();
                session.close();
            }
        }

Tests Results : 

  • Time taken:- 185s to 200s
  • Heap Pattern:- gradually increased from 13mb to 41mb (measured same using jconsole)

Using ScrollableResults with FORWARD_ONLY scroll mode in a StatelessSession.

A stateless session does not implement a first-level cache nor interact with any second-level cache, nor does it implement transactional write-behind or automatic dirty checking, nor do operations cascade to associated instances. Collections are ignored by a stateless session. Operations performed via a stateless session bypass Hibernate’s event model and interceptors.   

These type of session is always recommended in case of bulk updation as we really do not need these overheads of hibernate features in these type of usecases . 

Code Executed : 

 StatelessSession session = getStatelessSession();
        Transaction transaction = session.beginTransaction();
        ScrollableResults scrollableResults = session
                .createQuery("FROM PersonEntity WHERE id > " + MAX_ID_VALUE + " ORDER BY id")
                .setMaxResults(TRANSACTION_BATCH_SIZE).scroll(ScrollMode.FORWARD_ONLY);
        try {
            while (scrollableResults.next()) {
                PersonEntity personEntity = (PersonEntity) scrollableResults.get(0);
                personEntity.setName(randomAlphaNumeric(20));
                session.update(personEntity);
            }
        } finally {
            if (session != null && session.isOpen()) {
                transaction.commit();
                session.close();
            }
        }

Tests Results : 

  • Time taken:- 185s to 200s
  • Heap Pattern:- gradually increased from 13mb to 39mb

I also performed the same tests with 2000 rows and the results obtained were as follows:-

Results:-

  • Using list():- time taken:- approx 750s, heap pattern:- gradually increased from 13mb to 74 mb
  • Using ScrollableResultSet:- time taken:- approx 380s, heap pattern:- gradually increased from 13mb to 46mb
  • Using Stateless:- time taken:- approx 380s, heap pattern:- gradually increased from 13mb to 43mb

Blocker Problem with all above approaches Tried

ScrollableResults and Stateless ScrollableResults give almost the same performance which is much better than Query.list(). But there is still one problem with all the above approaches. Locking, all the above approaches select and update the data in same transaction, this means for as long as the transaction is running, the rows on which updates have been performed will be locked and any other operations will have to wait for the transaction to finish.

Solution : 

There are two things which we should do here to solve above problem : 

  •  we need to select and update data in different transactions.
  • And updation of these types should be done in Batches

So again I performed the same tests as above but this time update was performed in a different transaction which was commited in batches of 50.

Note:- In case of Scrollable and Stateless we need a different session also, as we need the original session and transaction to scroll through the results.

Results using Batch Processing

  • Using list():- time taken:- approx 400s, heap pattern:- gradually increased from 13mb to 61 mb
  • Using ScrollableResultSet:- time taken:- approx 380s, heap pattern:- gradually increased from 13mb to 51mb
  • Using Stateless:- time taken:- approx 190s, heap pattern:- gradually increased from 13mb to 44mb

Observation:- This temporal performance of ScrollableResults dropped down to become almost equal to Query.list(), but performance of Stateless remained almost same.

Summary and Conclusion

As from all the above experimentation  , in cases where we need to do bulk selection and updation, the best approach in terms of memory consumption and time is as follows : 

  • Use ScrollableResults in a Stateless Session.
  • Perform selection and updation in different transactions in batches of 20 to 50 (Batch Processing) (Note -*-  Batch size  can depend on the case to case basis)

  Sample Code with the best approach

  StatelessSession session = getStatelessSession();
        Transaction transaction = session.beginTransaction();
        ScrollableResults scrollableResults = session
                .createQuery("FROM PersonEntity WHERE id > " + MAX_ID_VALUE + " ORDER BY id")
                .setMaxResults(TRANSACTION_BATCH_SIZE).scroll(ScrollMode.FORWARD_ONLY);
        int count = 0;
        try {
            StatelessSession updateSession = getStatelessSession();
            Transaction updateTransaction = updateSession.beginTransaction();
            while (scrollableResults.next()) {
                PersonEntity personEntity = (PersonEntity) scrollableResults.get(0);
                personEntity.setName(randomAlphaNumeric(5));
                updateSession.update(personEntity);
                if (++count % 50 == 0) {
                    updateTransaction.commit();
                    updateTransaction = updateSession.beginTransaction();
                }
            }
            updateSession.close();
        } finally {
            if (session != null && session.isOpen()) {
                transaction.commit();
                session.close();
            }
        }

With the   java frameworks like spring and others this code may be even more smaller , like one not needing to  take care of session closing etc . Above code is written in plain java using hibernate. 

Please  try with large data and comment us the results , Also if you have some other better approach to do this please comment . 

Thank You for reading the article

Vulnerability Updates | Week of June 14, 2021

The CISA Vulnerability Bulletin provides a summary of new vulnerabilities that have been recorded by the National Institute of Standards and Technology (NIST) National Vulnerability Database (NVD) in the past week. NVD is sponsored by CISA. In some cases, the vulnerabilities in the bulletin may not yet have assigned CVSS scores. Please visit NVD for updated vulnerability entries, which include CVSS scores once they are available.

Vulnerabilities are based on the Common Vulnerabilities and Exposures (CVE) vulnerability naming standard and are organized according to severity, determined by the Common Vulnerability Scoring System (CVSS) standard. The division of high, medium, and low severities correspond to the following scores:

  • High: vulnerabilities with a CVSS base score of 7.0–10.0
  • Medium: vulnerabilities with a CVSS base score of 4.0–6.9
  • Low: vulnerabilities with a CVSS base score of 0.0–3.9

In this Blog , i am writing about High vulnerabilities only and some of Medium and Low if they it feels important to me .

For list of all vulnerabilities you can check CISA Bulletin .

High Vulnerabilities

Primary
Vendor — Product
DescriptionPublishedCVSS ScoreSource & Patch Info
bloofox — bloofoxcmsbloofoxCMS 0.5.2.1 is infected with Unrestricted File Upload that allows attackers to upload malicious files (ex: php files).2021-06-167.5CVE-2020-35760
MISC
google — androidIn avrc_msg_cback of avrc_api.cc, there is a possible out of bounds write due to a heap buffer overflow. This could lead to remote code execution with no additional execution privileges needed. User interaction is not needed for exploitation.Product: AndroidVersions: Android-11 Android-8.1 Android-9 Android-10Android ID: A-1776119582021-06-1110CVE-2021-0474
MISC
google — androidIn memory management driver, there is a possible out of bounds write due to a missing bounds check. This could lead to local escalation of privilege with no additional execution privileges needed. User interaction is not needed for exploitation.Product: AndroidVersions: Android SoCAndroid ID: A-1834648662021-06-117.2CVE-2021-0489
MISC
google — androidIn memory management driver, there is a possible memory corruption due to a double free. This could lead to local escalation of privilege with no additional execution privileges needed. User interaction is not needed for exploitation.Product: AndroidVersions: Android SoCAndroid ID: A-1834613212021-06-117.2CVE-2021-0498
MISC
google — androidIn memory management driver, there is a possible memory corruption due to a use after free. This could lead to local escalation of privilege with no additional execution privileges needed. User interaction is not needed for exploitation.Product: AndroidVersions: Android SoCAndroid ID: A-1834613202021-06-117.2CVE-2021-0497
MISC
google — androidIn memory management driver, there is a possible memory corruption due to a use after free. This could lead to local escalation of privilege with no additional execution privileges needed. User interaction is not needed for exploitation.Product: AndroidVersions: Android SoCAndroid ID: A-1834679122021-06-117.2CVE-2021-0496
MISC
google — androidIn memory management driver, there is a possible out of bounds write due to uninitialized data. This could lead to local escalation of privilege with no additional execution privileges needed. User interaction is not needed for exploitation.Product: AndroidVersions: Android SoCAndroid ID: A-1834590832021-06-117.2CVE-2021-0495
MISC
google — androidIn memory management driver, there is a possible out of bounds write due to an integer overflow. This could lead to local escalation of privilege with no additional execution privileges needed. User interaction is not needed for exploitation.Product: AndroidVersions: Android SoCAndroid ID: A-1834613182021-06-117.2CVE-2021-0494
MISC
google — androidIn memory management driver, there is a possible out of bounds write due to a missing bounds check. This could lead to local escalation of privilege with no additional execution privileges needed. User interaction is not needed for exploitation.Product: AndroidVersions: Android SoCAndroid ID: A-1834613172021-06-117.2CVE-2021-0493
MISC
google — androidIn memory management driver, there is a possible out of bounds write due to a missing bounds check. This could lead to local escalation of privilege with no additional execution privileges needed. User interaction is not needed for exploitation.Product: AndroidVersions: Android SoCAndroid ID: A-1834590782021-06-117.2CVE-2021-0492
MISC
google — androidIn memory management driver, there is a possible escalation of privilege due to a missing permission check. This could lead to local escalation of privilege with no additional execution privileges needed. User interaction is not needed for exploitation.Product: AndroidVersions: Android SoCAndroid ID: A-1834613152021-06-117.2CVE-2021-0491
MISC
google — androidIn memory management driver, there is a possible out of bounds write due to a missing bounds check. This could lead to local escalation of privilege with no additional execution privileges needed. User interaction is not needed for exploitation.Product: AndroidVersions: Android SoCAndroid ID: A-1834648682021-06-117.2CVE-2021-0490
MISC
google — androidIn onCreate of CalendarDebugActivity.java, there is a possible way to export calendar data to the sdcard without user consent due to a tapjacking/overlay attack. This could lead to local escalation of privilege with User execution privileges needed. User interaction is not needed for exploitation.Product: AndroidVersions: Android-11Android ID: A-1740463972021-06-117.2CVE-2021-0487
MISC
google — androidIn onActivityResult of EditUserPhotoController.java, there is a possible access of unauthorized files due to an unexpected URI handler. This could lead to local escalation of privilege with no additional execution privileges needed. User interaction is needed for exploitation.Product: AndroidVersions: Android-8.1 Android-9 Android-10 Android-11Android ID: A-1729391892021-06-119.3CVE-2021-0481
MISC
google — androidIn getMinimalSize of PipBoundsAlgorithm.java, there is a possible bypass of restrictions on background processes due to a permissions bypass. This could lead to local escalation of privilege with no additional execution privileges needed. User interaction is not needed for exploitation.Product: AndroidVersions: Android-11Android ID: A-1743026162021-06-117.2CVE-2021-0485
MISC
google — androidIn notifyScreenshotError of ScreenshotNotificationsController.java, there is a possible permission bypass due to an unsafe PendingIntent. This could lead to local escalation of privilege with User execution privileges needed. User interaction is not needed for exploitation.Product: AndroidVersions: Android-10 Android-11 Android-8.1 Android-9Android ID: A-1781892502021-06-117.2CVE-2021-0477
MISC
google — androidAn improper input validation vulnerability in sflacfd_get_frm() in libsflacextractor library prior to SMR MAY-2021 Release 1 allows attackers to execute arbitrary code on mediaextractor process.2021-06-117.5CVE-2021-25387
MISC
google — androidAn improper input validation vulnerability in sdfffd_parse_chunk_FVER() in libsdffextractor library prior to SMR MAY-2021 Release 1 allows attackers to execute arbitrary code on mediaextractor process.2021-06-117.5CVE-2021-25386
MISC
google — androidAn improper input validation vulnerability in sdfffd_parse_chunk_PROP() in libsdffextractor library prior to SMR MAY-2021 Release 1 allows attackers to execute arbitrary code on mediaextractor process.2021-06-117.5CVE-2021-25385
MISC
google — androidAn improper input validation vulnerability in sdfffd_parse_chunk_PROP() with Sample Rate Chunk in libsdffextractor library prior to SMR MAY-2021 Release 1 allows attackers to execute arbitrary code on mediaextractor process.2021-06-117.5CVE-2021-25384
MISC
google — androidAn improper input validation vulnerability in scmn_mfal_read() in libsapeextractor library prior to SMR MAY-2021 Release 1 allows attackers to execute arbitrary code on mediaextractor process.2021-06-117.5CVE-2021-25383
MISC
google — androidIn on_l2cap_data_ind of btif_sock_l2cap.cc, there is possible memory corruption due to a use after free. This could lead to remote code execution over Bluetooth with no additional execution privileges needed. User interaction is not needed for exploitation.Product: AndroidVersions: Android-11 Android-10Android ID: A-1756861682021-06-118.3CVE-2021-0475
MISC
google — androidIn rw_t3t_process_error of rw_t3t.cc, there is a possible double free due to uninitialized data. This could lead to remote code execution over NFC with no additional execution privileges needed. User interaction is not needed for exploitation.Product: AndroidVersions: Android-9 Android-10 Android-11 Android-8.1Android ID: A-1796872082021-06-118.3CVE-2021-0473
MISC
google — androidAn improper access control vulnerability in genericssoservice prior to SMR JUN-2021 Release 1 allows local attackers to execute protected activity with system privilege via untrusted applications.2021-06-117.2CVE-2021-25412
MISC

For the complete list Please visit https://us-cert.cisa.gov/ncas/bulletins/sb21-172

Please subscribe hello-worlds.in for more updates 

Vulnerability Updates | Week of June 7, 2021

The CISA Vulnerability Bulletin provides a summary of new vulnerabilities that have been recorded by the National Institute of Standards and Technology (NIST) National Vulnerability Database (NVD) in the past week. NVD is sponsored by CISA. In some cases, the vulnerabilities in the bulletin may not yet have assigned CVSS scores. Please visit NVD for updated vulnerability entries, which include CVSS scores once they are available.

Vulnerabilities are based on the Common Vulnerabilities and Exposures (CVE) vulnerability naming standard and are organized according to severity, determined by the Common Vulnerability Scoring System (CVSS) standard. The division of high, medium, and low severities correspond to the following scores:

  • High: vulnerabilities with a CVSS base score of 7.0–10.0
  • Medium: vulnerabilities with a CVSS base score of 4.0–6.9
  • Low: vulnerabilities with a CVSS base score of 0.0–3.9

In this Blog , i am writing about High vulnerabilities only and some of Medium and Low if they it feels important to me .

For list of all vulnerabilities you can check CISA Bulletin .

High Vulnerabilities

ProductDescriptionPublishedCVSS ScoreSource & Patch Info
linux — linux_kernelThe eBPF RINGBUF bpf_ringbuf_reserve() function in the Linux kernel did not check that the allocated size was smaller than the ringbuf size, allowing an attacker to perform out-of-bounds writes within the kernel and therefore, arbitrary code execution. This issue was fixed via commit 4b81ccebaeee (“bpf, ringbuf: Deny reserve of buffers larger than ringbuf”) (v5.13-rc4) and backported to the stable kernels in v5.12.4, v5.11.21, and v5.10.37. It was introduced via 457f44363a88 (“bpf: Implement BPF ring buffer and verifier support for it”) (v5.8-rc1).2021-06-047.2CVE-2021-3489
MISC
UBUNTU
UBUNTU
MISC
MLIST
linux — linux_kernelThe eBPF ALU32 bounds tracking for bitwise ops (AND, OR and XOR) in the Linux kernel did not properly update 32-bit bounds, which could be turned into out of bounds reads and writes in the Linux kernel and therefore, arbitrary code execution. This issue was fixed via commit 049c4e13714e (“bpf: Fix alu32 const subreg bound tracking on bitwise operations”) (v5.13-rc4) and backported to the stable kernels in v5.12.4, v5.11.21, and v5.10.37. The AND/OR issues were introduced by commit 3f50f132d840 (“bpf: Verifier, do explicit ALU32 bounds tracking”) (5.7-rc1) and the XOR variant was introduced by 2921c90d4718 (“bpf:Fix a verifier failure with xor”) ( 5.10-rc1).2021-06-047.2CVE-2021-3490
UBUNTU
MISC
MISC
UBUNTU
MLIST
linux — linux_kernelThe io_uring subsystem in the Linux kernel allowed the MAX_RW_COUNT limit to be bypassed in the PROVIDE_BUFFERS operation, which led to negative values being usedin mem_rw when reading /proc/<PID>/mem. This could be used to create a heap overflow leading to arbitrary code execution in the kernel. It was addressed via commit d1f82808877b (“io_uring: truncate lengths larger than MAX_RW_COUNT on provide buffers”) (v5.13-rc1) and backported to the stable kernels in v5.12.4, v5.11.21, and v5.10.37. It was introduced in ddf0322db79c (“io_uring: add IORING_OP_PROVIDE_BUFFERS”) (v5.7-rc1).2021-06-047.2CVE-2021-3491
UBUNTU
UBUNTU
MISC
MISC
MLIST
microsoft — intune_management_extensionMicrosoft Intune Management Extension Remote Code Execution Vulnerability2021-06-087.5CVE-2021-31980
MISC
microsoft — windows_10Server for NFS Information Disclosure Vulnerability This CVE ID is unique from CVE-2021-31976.2021-06-087.8CVE-2021-31975
MISC
microsoft — windows_10Server for NFS Information Disclosure Vulnerability This CVE ID is unique from CVE-2021-31975.2021-06-087.8CVE-2021-31976
MISC
microsoft — windows_10Kerberos AppContainer Security Feature Bypass Vulnerability2021-06-087.5CVE-2021-31962
MISC
qualcomm — apq8009_firmwareOut of bound read will happen if EAPOL Key length is less than expected while processing NAN shared key descriptor attribute in Snapdragon Auto, Snapdragon Compute, Snapdragon Connectivity, Snapdragon Consumer Electronics Connectivity, Snapdragon Consumer IOT, Snapdragon Industrial IOT, Snapdragon IoT, Snapdragon Mobile, Snapdragon Voice & Music, Snapdragon Wired Infrastructure and Networking2021-06-097.8CVE-2020-11241
CONFIRM

Medium Vulnerabilities

Primary
Vendor — Product
DescriptionPublishedCVSS ScoreSource & Patch Info
openvpn — openvpn_access_server
OpenVPN Access Server 2.7.3 to 2.8.7 allows remote attackers to trigger an assert during the user authentication phase via incorrect authentication token data in an early phase of the user authentication resulting in a denial of service.2021-06-04
5CVE-2020-36382
MISC

MISC

For the complete list Please visit https://us-cert.cisa.gov/ncas/bulletins/sb21-165

Please subscribe hello-worlds.in for more updates .

Automatic indexes recommendations in PostgreSQL

In our last blog we learned about  Need and Usage of Hypothetical indexes in Postgresql  . Now we can check easily in live environment also if some particular index will be helpful or not , but  how we get to know which index to test  .  It requires in-depth knowledge of indexing and experience in Postgresql , But in PostgreSql we can get automatic recommendation of indexes for specific queries by using three extensions hypog,pg_stat_statements,pg_qualstats

Now Let’s move directly to the practical part how we can use  this feature in postgres  : 

i am doing experiments on Postgres10 installed on Centos7 .

Installation 

yum install pg_qualstats10.x86_64
  • Change the following in postgresql.conf and restart postgresql 
shared_preload_libraries = 'pg_stat_statements, pg_qualstats' 
  • Create following extensions : 
testdb=# CREATE EXTENSION hypopg ;
CREATE EXTENSION
testdb=# CREATE EXTENSION pg_stat_statements ;
CREATE EXTENSION
testdb=# CREATE EXTENSION pg_qualstats;
CREATE EXTENSION
  • Set sample rate of pgqual stats to 1 in postgresql.conf  . This rate define how frequently monitor  and analyze the queries . Value ‘1’ represents that keep track of all queries
pg_qualstats.sample_rate = '1'
  • Create the function which will be used to detect usable indexes : 
CREATE OR REPLACE FUNCTION find_usable_indexes()
RETURNS VOID AS
$$
DECLARE
    l_queries     record;
    l_querytext     text;
    l_idx_def       text;
    l_bef_exp       text;
    l_after_exp     text;
    hypo_idx      record;
    l_attr        record;
    /* l_err       int; */
BEGIN
    CREATE TABLE IF NOT EXISTS public.idx_recommendations (queryid bigint, 
    query text, current_plan jsonb, recmnded_index text, hypo_plan jsonb);
    FOR l_queries IN
    SELECT t.relid, t.relname, t.queryid, t.attnames, t.attnums, 
    pg_qualstats_example_query(t.queryid) as query
      FROM 
        ( 
         SELECT qs.relid::regclass AS relname, qs.relid AS relid, qs.queryid, 
         string_agg(DISTINCT attnames.attnames,',') AS attnames, qs.attnums
         FROM pg_qualstats_all qs
         JOIN pg_qualstats q ON q.queryid = qs.queryid
         JOIN pg_stat_statements ps ON q.queryid = ps.queryid
         JOIN pg_amop amop ON amop.amopopr = qs.opno
         JOIN pg_am ON amop.amopmethod = pg_am.oid,
         LATERAL 
              ( 
               SELECT pg_attribute.attname AS attnames
               FROM pg_attribute
               JOIN unnest(qs.attnums) a(a) ON a.a = pg_attribute.attnum 
               AND pg_attribute.attrelid = qs.relid
               ORDER BY pg_attribute.attnum) attnames,     
         LATERAL unnest(qs.attnums) attnum(attnum)
               WHERE NOT 
               (
                EXISTS 
                      ( 
                       SELECT 1
                       FROM pg_index i
                       WHERE i.indrelid = qs.relid AND 
                       (arraycontains((i.indkey::integer[])[0:array_length(qs.attnums, 1) - 1], 
                        qs.attnums::integer[]) OR arraycontains(qs.attnums::integer[], 
                        (i.indkey::integer[])[0:array_length(i.indkey, 1) + 1]) AND i.indisunique)))
                       GROUP BY qs.relid, qs.queryid, qs.qualnodeid, qs.attnums) t
                       GROUP BY t.relid, t.relname, t.queryid, t.attnames, t.attnums                   
    LOOP
        /* RAISE NOTICE '% : is queryid',l_queries.queryid; */
        execute 'explain (FORMAT JSON) '||l_queries.query INTO l_bef_exp;
        execute 'select hypopg_reset()';
        execute 'SELECT indexrelid,indexname FROM hypopg_create_index(''CREATE INDEX on '||l_queries.relname||'('||l_queries.attnames||')'')' INTO hypo_idx;      
        execute 'explain (FORMAT JSON) '||l_queries.query INTO l_after_exp;
        execute 'select hypopg_get_indexdef('||hypo_idx.indexrelid||')' INTO l_idx_def;
        INSERT INTO public.idx_recommendations (queryid,query,current_plan,recmnded_index,hypo_plan) 
        VALUES (l_queries.queryid,l_querytext,l_bef_exp::jsonb,l_idx_def,l_after_exp::jsonb);        
    END LOOP;    
        execute 'select hypopg_reset()';
END;
$$ LANGUAGE plpgsql;

Usage 

  • Now Let’s make a table with 10 Crores rows as following : 
testdb=# create table orders as select s as orderno, md5(random()::text) as orderitem , now() as order_created from generate_Series(1,100000000) s;
SELECT 100000000
  • Now lets run a query on which we want to check if need to make any index  
testdb=# select * from orders where orderno = 80000  ;
-[ RECORD 1 ]-+---------------------------------
orderno       | 80000
orderitem     | 03b41c2f32d99e9a597010608946c4c6
order_created | 2021-05-22 17:52:21.435936+05:30
  • Now run following queries to find out  which indexes are recommend by this extension and what are the improvement percentage by applying these indexes hypothetically 

testdb=#           select find_usable_indexes();
 find_usable_indexes 
---------------------
 
(1 row)


testdb=# select b.query, a.recmnded_index,round((((a.current_plan->0->'Plan'->>'Total Cost')::numeric-(hypo_plan->0->'Plan'->>'Total Cost')::numeric)*100/(a.current_plan->0->'Plan'->>'Total Cost')::numeric),2) as percent_improvd FROM idx_recommendations a JOIN pg_stat_statements b ON a.queryid = b.queryid WHERE round((((current_plan->0->'Plan'->>'Total Cost')::numeric-(hypo_plan->0->'Plan'->>'Total Cost')::numeric)*100/(current_plan->0->'Plan'->>'Total Cost')::numeric),2) > 0 order by 3 desc ;
                        query                        |                          recmnded_index                          | percent_improvd 
-----------------------------------------------------+------------------------------------------------------------------+-----------------
 select * from orders where orderno = $1             | CREATE INDEX ON public.orders USING btree (orderno)              |          100.00

Above  analysis was internally done by creating the indexes hypothetically not by making real indexes .

Please Note here that do not fully rely on automatic index recommendation . Yes we have no doubt it is very very useful feature  , but please also check logically why these recommended indexes are useful and should you really create it or not. 

You can read PostgreSQL Index Tutorial Series for basic in depth understanding of indexes in postgresql . 

Now , You can try the explained feature with more complex queries and comment on this article explaining your result  with your queries . So let’s experiment and comment . 

Refrences : https://www.percona.com/blog/2019/07/22/automatic-index-recommendations-in-postgresql-using-pg_qualstats-and-hypopg/

Hypothetical Indexes in PostgreSQL – Need and Usage

In this Blog We will first cover what are hypothetical indexes and what is need of these type of indexes and then secondly  we shall see the practical usage of these indexes . 

Need of Hypothetical Indexes : 

As the name suggests   these are not real indexes,  these are hypothetical indexes i.e.. They are virtual indexes which PostgreSQL  query planner does not consider when running queries . 

 Now the question arises where these Hypothetical Indexes  are useful  ? ? ? 

First Let’s discuss one scenario  , we have a large table which is currently in the production environment  and we need to make some index on live db and we are not sure whether that index will be useful or not , we even don’t know if  by making that index our production environment may be down !!!

So , Solution of above problem will be following : 

  • Lets ignore the risk and make  the index on live table which can result the following scenario 
    • first of all it will take lots of time depending on data present in table 
    • Live queries may get affected badly if we are not sure if the index we are making will increase or decrease the cost .
    • We also do not know the size of the index it may be too large which can again impact the production database server . 
  • Another solution is to replicate the production database to the local dev environment and then apply all the hits and try there and then apply at the production environment .  it seems a very safe and effective approach in almost all cases but this will take too much of time in setting up the things and testing with actual scenario .  
  • Third Solution is Hypothetical Indexes  as this functionality will create imaginary indexes not real indexes .  But  there are some things to note about these indexes :
    •  it creates an index in our connection’s private memory. None of the catalog tables nor the original tables are actually touched. 
    • The only way to see if we can benefit from that index is by running an EXPLAIN <QUERY>.
    •  If you wish to run an EXPLAIN ANALYZE that runs that SQL and provides the run time stats, it would not be able to use that hypothetical index. This is because a hypothetical index does not exist in reality.
    • Currently these indexes will work on BTREE ONLY . However you can try if it works on other type of indexes .

Usage of Hypothetical indexes : 

Installation 

I am using PostgreSQL10 on CentOS7 . 

  • Download hypopg by following command : 
Wget  https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/hypopg_10-1.1.4-1.rhel7.x86_64.rpm
  • Then install it on CentOS7 :
yum install hypopg_10-1.1.4-1.rhel7.x86_64.rpm
  • Now create extension using following query 
testdb=# CREATE EXTENSION hypopg ;
CREATE EXTENSION
  • On Creating extension following functions will be created . 
testdb=#  select proname from pg_proc where proname ilike '%hyp%';
       proname        
----------------------
 hypopg_reset_index
 hypopg_reset
 hypopg_create_index
 hypopg_drop_index
 hypopg
 hypopg_list_indexes
 hypopg_relation_size
 hypopg_get_indexdef

Usage 

Now Let’s make a table with 10 Crores rows as following : 


testdb=# create table orders as select s as orderno, md5(random()::text) as orderitem , now() as order_created from generate_Series(1,100000000) s;
SELECT 100000000

Now check COST of a query by running explain : 

testdb=# explain select * from orders where orderno > 80000  order by order_created desc  limit 100 ;
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Limit  (cost=3600088.98..3600089.23 rows=100 width=44)
   ->  Sort  (cost=3600088.98..3688095.27 rows=35202513 width=44)
         Sort Key: order_created DESC
         ->  Seq Scan on orders  (cost=0.00..2254674.25 rows=35202513 width=44)
               Filter: (orderno > 80000)
(5 rows)

Now create the Hypothetical Index : 

testdb=# SELECT * FROM hypopg_create_index('create index order_created_idx on orders(order_created)');
 indexrelid |             indexname             
------------+-----------------------------------
      24797 | <24797>btree_orders_order_created
(1 row)

Now again do Explain to check if above index may be useful or not : 

testdb=# explain select * from orders where orderno > 80000  order by order_created desc  limit 100 ;
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.07..4.29 rows=100 width=45)
   ->  Index Scan Backward using "<24797>btree_orders_order_created" on orders  (cost=0.07..4215496.19 rows=99917459 width=45)
         Filter: (orderno > 80000)
(3 rows)

from both Explain command output we can clearly see the diffrence in cost and can also see that planner is using newly created hypothetical index .

We can Drop the index as follows : 

testdb=# select * from hypopg_drop_index(24797);
 hypopg_drop_index 
-------------------
 t
(1 row)

We can also check the estimated size of index created virtually as follows : 

testdb=# SELECT * FROM hypopg_create_index('create index order_created_idx on orders(order_created)');
 indexrelid |             indexname             
------------+-----------------------------------
      24798 | <24798>btree_orders_order_created
(1 row)

testdb=# select * from pg_size_pretty(hypopg_relation_size(24798));
 pg_size_pretty 
----------------
 2990 MB
(1 row)

Now lets create actual index and see what will be the actual size

testdb=# create index order_created_idx on orders(order_created);
CREATE INDEX
testdb=# \di+ order_created_idx
                               List of relations
 Schema |       Name        | Type  |  Owner   | Table  |  Size   | Description 
--------+-------------------+-------+----------+--------+---------+-------------
 public | order_created_idx | index | postgres | orders | 2142 MB | 
(1 row)

As seen estimated and actual size is comparable .

I  Hope it clears the usage if hypothetical index in PostgreSQL .  In one of our blogs we learned about why index is not working  and also how to check on which tables index needed .  

In our future blogs we will talk about how you will get to know about exact index you need to make in database   . 

Stay Tuned to hello worlds . . . 

References : https://www.percona.com/blog/2019/06/21/hypothetical-indexes-in-postgresql/

Load Average in Linux Servers – Confusion Solved

Regarding Load Average  shown in Linux there are many confusion around the world  like 

  • What is load average show in top command ?
  • What this values represent ? 
  • When it will be high when low ?
  • When to consider it as critical ? 
  • In which scenarios it can increase ? 

In this Blog we will talk about the answers of all these  . 

What are these three values shown in above image  ? 

The three numbers represent averages over progressively longer periods of time (one, five, and fifteen-minute averages)  and that lower numbers are better. Higher numbers represent a problem or an overloaded machine . 

Now before getting into what is good value, what is bad value , what are the reasons which can affect these values  , We will understand these on  a machine with one single-core processor. 

The traffic analogy

A single-core CPU is like a single lane of traffic. Imagine you are a bridge operator … sometimes your bridge is so busy there are cars lined up to cross. You want to let folks know how traffic is moving on your bridge. A decent metric would be how many cars are waiting at a particular time. If no cars are waiting, incoming drivers know they can drive across right away. If cars are backed up, drivers know they’re in for delays.

So, Bridge Operator, what numbering system are you going to use? How about:

  • 0.00 means there’s no traffic on the bridge at all. In fact, between 0.00 and 1.00 means there’s no backup, and an arriving car will just go right on.
  • 1.00 means the bridge is exactly at capacity. All is still good, but if traffic gets a little heavier, things are going to slow down.
  • over 1.00 means there’s backup. How much? Well, 2.00 means that there are two lanes worth of cars total — one lane’s worth on the bridge, and one lane’s worth waiting. 3.00 means there are three lanes worth total — one lane’s worth on the bridge, and two lanes’ worth waiting. Etc.

Like the bridge operator, you’d like your cars/processes to never be waiting. So, your CPU load should ideally stay below 1.00. Also, like the bridge operator, you are still ok if you get some temporary spikes above 1.00 … but when you’re consistently above 1.00, you need to worry.

So you’re saying the ideal load is 1.00?

Well, not exactly. The problem with a load of 1.00 is that you have no headroom. In practice, many sysadmins will draw a line at 0.70:

But now a days we many multiple cores systems or multiple processors system  .

Got a quad-processor system? It’s still healthy with a load of 3.00.

On a multi-processor system, the load is relative to the number of processor cores available. The “100% utilization” mark is 1.00 on a single-core system, 2.00, on a dual-core, 4.00 on a quad-core, etc.

If we go back to the bridge analogy, the “1.00” really means “one lane’s worth of traffic”. On a one-lane bridge, that means it’s filled up. On a two-lane bridge, a load of 1.00 means it’s at 50% capacity — only one lane is full, so there’s another whole lane that can be filled.

Same with CPUs: a load of 1.00 is 100% CPU utilization on a single-core box. On a dual-core box, a load of 2.00 is 100% CPU utilization.

Which leads us to two new Rules of Thumb:

  • The “number of cores = max load” Rule of Thumb: on a multicore system, your load should not exceed the number of cores available.
  • The “cores is cores” Rule of Thumb: How the cores are spread out over CPUs doesn’t matter. Two quad-cores == four dual-cores == eight single-cores. It’s all eight cores for these purposes.

But What to extract from here that if load is going beyond number of Cores are we in crunch of Cores ? 

Well  Not exactly , For this We need to further debug or analyse TOP command data to come to a conclusion  . 

In above output , This coloured part shows CPU used by user process(us) and by system process(sy) . Now if these values are around 99-100%, it means there is crunch of cpu cores on your system or some process is consuming more CPU . So, in this case either increase cores or optimise you application which is consuming more CPU . 

Now let’s take another scenario : 

In above image  , coloured parts shows amount of time CPU is waiting in doing Input/Output(I/O) .  So say if this values is going above say 80% ,then also load average on server will increase  . It means either you disk read/write speed is slow  or your applications is reading/writing too much on your system beyond system capability . In this case either diagnose your hard disk read/write speed or check why your application is reading/writing so much . 

Now let’s take one more scenario : 

If values Above coloured output goes beyond certain limit , it means softirq(si) are consuming cpu  . it can be due to network/disk interrupts . Either they are not getting enough CPU   or there is some misconfiguration in you ethernet ports due to which  interrupts are not able to handle packets receiving or transmitting  . These types of problem occurs more on VM environment rather than physical machine  . 

Now , Lest take one last scenario : 

This above part will help you in case of Virtual Machine Environment  . 

If %st increases to certain limit say it is remaining more than 50% , it means that you are getting half of CPU time from base machine and someone else is consuming you CPU time as you may be on shared CPU infra  . In above case also Load can increase  . 

I hope it covers all major scenarios in which load average can increase  . 

Now there are many points open here like how to check read/write speed, how to check which application is consuming more CPU , How to check which interrupts are causing problem . For all of these stay tuned to hello worlds .

Refrences : https://scoutapm.com/blog/understanding-load-averages

Checklists – System is Hacked – Part 2 – Preventive Steps for Infra (OS Hardening)

In last article we described List of Checks which can determine if system is compromised or hacked .  In this article we will talk about preventive steps (specially infra related) can be taken care to avoid hacking or to make system more secure  . There are many directions in which we can secure our application  as follows : 

  • OS hardening (Infra Level Security)
  • Secure Coding guidelines
  • Encryption Of Sensitive Data  . 
  • Ensure No Vulnerability exists in system . 

In this Blog we will be concerned about OS hardening (Infra Level Security) in Linux systems(CentOS/Redhat). We will Cover Other parts in Future Blogs .

Now Let’s go to the System Part. It has following things to be taken care of : 

  • SSH Configuration :  
    • In linux based system SSH default port is 22 . This Defaut port should be changed to some unused port to enhance security .  
    • Use SSH Protocol 2 Version
    • Ensure SSH X11 forwarding is disabled
  • Port Configuration at Firewall :  Generally , in any application there are many applications running on set of servers and each running on some different ports , Say for example : 
    • Application server at  8080 port
    • Database Server at 5432 port

So,  as in above Case Users need to login through 8080 port so only this port should be opened for public as Database needs to interact generally with application server so 5432 port should be allowed from Application Server’s IP  . 

  • Multi Factor Authentication for SSH should be enabled   —  For setting up Google Authentication on CentOS or Redhat you can follow the link
  • Root login for any server must be disabled 
  • Server Login Policies 
    • Ensure password expiration is 365 days or less 
    • Ensure minimum days between password changes is 7 or more 
    • Ensure password expiration warning days is 7 or more 
    • Ensure inactive password lock is 30 days or less 
    • Ensure Password should be strong enough when user change its password
  • Application and Database should be on different Servers  :  this is because of that if due to some vulnerability  application hacked than acces to database in that case is protected  . 
  • Regular package updates   :  Configure Auto update or regularly update packages on all configured servers .
  • Tune Network Kernel Parameters :
    • IP forwarding should be disabled on all servers  
      • Do the following entry in sysctl.conf 
        • net.ipv4.ip_forward = 0
    • Packets Redirecting  should be diabled on all servers . 
      • Do the following entry in sysctl.conf 
        • net.ipv4.conf.all.send_redirects = 0
        • net.ipv4.conf.default.send_redirects = 0
  • Selinux should be enabled and configured . 
  • Antivirus must  be installed on all servers . 

All Above are basic minimum checklists which should be applied to all the servers in any production environment . For implementing in-depth OS Hardening specially for CentOS based Systems , one need to follow the latest CIS CentOS Benchmarklatest CIS

You can also check the below benchmark list from CIS for CentOS hardening : Below doc also explain how to implement things on CentOS .

For Other Operating Systems/Technologies follow the CIS benchmark link.  

In Our Future blog we will explain other parts like Secure Code guidelines , Encryption , VAPT scan etc  to make system more secure . 

Stay tuned . 

Tune Linux Kernel Parameters For PostgreSQL Optimization and better System Performance

Introduction

In my previous Article i explained  Tuning PostgreSQL Database Memory Configuration Parameters to Optimize Performance and as i said  Database performance does not only depend on Postgresql configurations but also on system parameters .Poorly configured OS kernel parameters can cause degradation in database server performance. Therefore, it is imperative that these parameters are configured according to the database server and its workload. In this article  i will be talking about centos/redhat  linux system specially . 

Story

I will start the article with small story where on one of our client huge amount of writes were there and customer have provided us 200 GB of RAM for that dedicated database server , So there were no problem of resources. 

Now what was happening that after sometime system loads get increased so much and on debugging we found no special query  around the time when load increases . Somewhere over internet we found if we clear the system cache  regularly then issue will be resolved . 

We then schedule a cron to clear system cache after some regular interval and issue got resolved . 

Now  the question is why issue was not coming after this ? ? ?

And the Answer is  that due to large cache size as we have so much of ram available   lots of data is collected in RAM (in GB’s) and when this whole data flushes out on to the disk   ,  system load becomes high at that time 

So from that we came to know that we also need to tune some system parameters also to optimize system and database(postgresql) performance . 

In above case we tuned vm.dirty_background_ratio and vm.dirty_ratio , these two system(os) parameters to resolve the issue . 

Kernel parameters Tuning

Now what values we set for these above two parameters described in story and what are all other  important Linux kernel parameters that can affect database server performance which we can tune are described as follows : 

vm.dirty_background_ratio / vm.dirty_background_bytes

The vm.dirty_background_ratio is the percentage of memory filled with dirty pages that need to be flushed to disk. Flushing is done in the background. The value of this parameter ranges from 0 to 100; however, a value lower than 5 may not be effective and some kernels do not internally support it. The default value is 10 on most Linux systems. You can gain performance for write-intensive operations with a lower ratio, which means that Linux flushes dirty pages in the background.

You need to set a value of vm.dirty_background_bytes depending on your disk speed.

There are no “good” values for these two parameters since both depend on the hardware. However, setting vm.dirty_background_ratio to 5 and vm.dirty_background_bytes to 25% of your disk speed improves performance by up to ~25% in most cases.

vm.dirty_ratio / dirty_bytes

This is the same as vm.dirty_background_ratio / dirty_background_bytes except that the flushing is done in the foreground, blocking the application. So vm.dirty_ratio should be higher than vm.dirty_background_ratio. This will ensure that background processes kick in before the foreground processes to avoid blocking the application, as much as possible. You can tune the difference between the two ratios depending on your disk IO

 vm.swappiness

vm.swappiness is another kernel parameter that can affect the performance of the database. This parameter is used to control the swappiness (swapping pages to and from swap memory into RAM) behavior on a Linux system. The value ranges from 0 to 100. It controls how much memory will be swapped or paged out. Zero means disable swap and 100 means aggressive swapping.

You may get good performance by setting lower values.

Setting a value of 0 in newer kernels may cause the OOM Killer (out of memory killer process in Linux) to kill the process. Therefore, you can be on the safe side and set the value to 1 if you want to minimize swapping. The default value on a Linux system is 60. A higher value causes the MMU (memory management unit) to utilize more swap space than RAM, whereas a lower value preserves more data/code in memory.

A smaller value is a good bet to improve performance in PostgreSQL.

vm.overcommit_memory / vm.overcommit_ratio

Applications acquire memory and free that memory when it is no longer needed. But in some cases, an application acquires too much memory and does not release it.  This can invoke the OOM killer. Here are the possible values for vm.overcommit_memory parameter with a description for each:

  1. Heuristic overcommit, Do it intelligently (default); based kernel heuristics
  2. Allow overcommit anyway
  3. Don’t over commit beyond the overcommit ratio.

Reference: https://www.kernel.org/doc/Documentation/vm/overcommit-accounting

vm.overcommit_ratio is the percentage of RAM that is available for overcommitment. A value of 50% on a system with 2 GB of RAM may commit up to 3 GB of RAM.

A value of 2 for vm.overcommit_memory yields better performance for PostgreSQL. This value maximizes RAM utilization by the server process without any significant risk of getting killed by the OOM killer process. An application will be able to overcommit, but only within the overcommit ratio, thus reducing the risk of having OOM killer kill the process. Hence a value to 2 gives better performance than the default 0 value. However, reliability can be improved by ensuring that memory beyond an allowable range is not overcommitted. It avoids the risk of the process being killed by OOM-killer.

On systems without swap, one may experience a problem when vm.overcommit_memory is 2.

https://www.postgresql.org/docs/current/static/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT

Generally speaking almost all applications which uses more memory depends on this , For example  , In Redis setting this value 1 is best . 

Turn On Huge Pages

Linux, by default, uses 4K memory pages, BSD has Super Pages, whereas Windows has Large Pages. A page is a chunk of RAM that is allocated to a process. A process may own more than one page depending on its memory requirements. The more memory a process needs the more pages that are allocated to it. The OS maintains a table of page allocation to processes. The smaller the page size, the bigger the table, the more time required to look up a page in that page table. Therefore, huge pages make it possible to use a large amount of memory with reduced overheads; fewer page lookups, fewer page faults, faster read/write operations through larger buffers. This results in improved performance.

PostgreSQL has support for bigger pages on Linux only. By default, Linux uses 4K of memory pages, so in cases where there are too many memory operations, there is a need to set bigger pages. Performance gains have been observed by using huge pages with sizes 2 MB and up to 1 GB. The size of Huge Page can be set boot time. You can easily check the huge page settings and utilization on your Linux box using cat /proc/meminfo | grep -i huge command.

Get HugePage Info – On Linux (only)

Note: This is only for Linux, for other OS this operation is ignored
$ cat /proc/meminfo | grep -i huge
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

In this example, although huge page size is set at 2,048 (2 MB), the total number of huge pages has a value of 0. which signifies that huge pages are disabled.

Script to quantify Huge Pages

This is a simple script which returns the number of Huge Pages required. Execute the script on your Linux box while your PostgreSQL is running. Ensure that $PGDATA environment variable is set to PostgreSQL’s data directory.

Get Number of Required HugePages

!/bin/bash
pid=head -1 $PGDATA/postmaster.pid
echo “Pid:            $pid”
peak=grep ^VmPeak /proc/$pid/status | awk '{ print $2 }'
echo “VmPeak:            $peak kB”
hps=grep ^Hugepagesize /proc/meminfo | awk '{ print $2 }'
echo “Hugepagesize:   $hps kB”
hp=$((peak/hps))
echo Set Huge Pages:     $hp

The output of the script looks like this:

Script Output

Pid:            12737
VmPeak:         180932 kB
Hugepagesize:   2048 kB
Set Huge Pages: 88

The recommended huge pages are 88, therefore you should set the value to 88.

Set HugePages Command :

sysctl -w vm.nr_hugepages= 88

Check the huge pages now, you will see no huge page is in use (HugePages_Free = HugePages_Total).

Again Get HugePage Info – On Linux (only)

$ cat /proc/meminfo | grep -i huge
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
HugePages_Total:      88
HugePages_Free:       88
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

Now set the parameter huge_pages “on” in $PGDATA/postgresql.conf and restart the server.

And Again Get HugePage Info – On Linux (only)

$ cat /proc/meminfo | grep -i huge
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
HugePages_Total:      88
HugePages_Free:       81
HugePages_Rsvd:       64
HugePages_Surp:        0
Hugepagesize:       2048 kB

Now you can see that a very few of the huge pages are used. Let’s now try to add some data into the database.

Some DB Operations to Utilise HugePages

postgres=# CREATE TABLE foo(a INTEGER);
CREATE TABLE
postgres=# INSERT INTO foo VALUES(generate_Series(1,10000000));
INSERT 0 10000000

Let’s see if we are now using more huge pages than before.

Once More Get HugePage Info – On Linux (only)

$ cat /proc/meminfo | grep -i huge
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
HugePages_Total:      88
HugePages_Free:       18
HugePages_Rsvd:        1
HugePages_Surp:        0
Hugepagesize:       2048 kB

Now you can see that most of the huge pages are in use.

Note: The sample value for HugePages used here is very low, which is not a normal value for a big production machine. Please assess the required number of pages for your system and set those accordingly depending on your system’s workload and resources.

Now, Tuning Postgresql parameters and kernel parameters is not enough for good Postgresql performance there are many other things like

  • How you are making Query
  • Proper Indexing — For this you can follow indexing series on our blog
  • Proper partitioning and sharding accroding to business usecase
  • and many more .

Stay tuned to get more blogs on optimizing postgresql performance

Refrences : https://www.percona.com/blog/2018/08/29/tune-linux-kernel-parameters-for-postgresql-optimization/