SQL Query with JSON column in Postgresql

I still use JSON datatypes in databases, and its proving to its worth. Postgresql loads the whole row during queries and this reduce access to other tables for join and retains data, instead of relying on dynamic changes of another table. JSONB type column is supported by Reportserver.net which I have mentioned in previous post. 

Here is an example to use JSONB type column in Postgresql 12 and newer. Difference from previous post, here its saved as an object instead of an array. Use of JSON or JSONB type is very much determined by the data intended for storage and retrieval.

Points to note:

Symbol ->

meaning: Get JSON array element (indexed from zero, negative integers count from the end)

Or Get JSON object field by key

Symbol: ->>

meaning: Get JSON array element as text Or Get JSON object field as text

Symbol: @>

meaning: Does the left JSON value contain the right JSON path/value entries at the top level?

Step 1: Create the sample database

CREATE TABLE public.products (

id serial NOT NULL,

"name" varchar(255) NOT NULL,

unit varchar NOT NULL, -- limit to 2 decimals

product jsonb NOT NULL,

created_at timestamp(0) NULL,

updated_at timestamp(0) NULL,

CONSTRAINT products_pkey PRIMARY KEY (id)

);

Step 2: Add sample data

Step 3: Run an SQL query

Display list of data as is.

SELECT id, name, product from products;

id|name       |product                                                                                                |

--|-----------|-------------------------------------------------------------------------------------------------------|

 3|Apple Pie  |{"id": 5, "name": "Apple Pie", "details": {"value": "1All-1-1000-xzp", "source": "Malaysia"}}          |

 1|Orange Bod |{"id": 18, "name": "Orange Gala", "details": {"value": "1Bll-1-99-aaa", "source": "Malaysia"}}         |

 2|Chicken Pie|{"id": 4, "name": "Downtown Chicken Pie", "details": {"value": "1Bll-1-201-aaa", "source": "Malaysia"}}|

Display columns id, name and product->details

SELECT id, name, product->'details' from products;

or

SELECT id, name, product->'details' as details from products;

Display columns id, name and sub contents of a json data

select id, name, (product->'details')::jsonb->'source' as details from products;

The WHERE clause

Lets retrieve rows where jsonb element key 'name' have a specific value.

SELECT id, name, product->'details' as details from products

where product->>'name'= 'Apple Pie'

Retrieve rows where jsonb element key 'name' have a wildcard (%) value.

select id, name, product->'details' as details from products

where product->>'name' like 'Apple %'

Retrieve rows where jsonb sub element key 'value' have a wildcard (%) value.

select id, name, product->'details' as details from products

where product->'details'->>'value'::text like  '1All-1-%'

Retrieve rows where jsonb element have a specific key:value

select id, name,  product as details from products

where product @> '{"name":"Orange Gala"}'

I hope these examples will benefit you.

Read copy update the RCU

On Linux, listing of processes will most likely show a process rcu_sched with tty having value of ? Here is an example of a command

ps -ef | grep rcu

UID PID PPID C STIME TTY TIME CMD

root 3 2 0 Jul06 ? 00:00:00 [rcu_gp]
root 4 2 0 Jul06 ? 00:00:00 [rcu_par_gp]
root 10 2 0 Jul06 ? 00:00:00 [rcu_tasks_rude_]
root 11 2 0 Jul06 ? 00:00:00 [rcu_tasks_trace]
root 13 2 0 Jul06 ? 00:03:37 [rcu_sched]

This is a solution on Linux where shared data structures is accessed by many resources. When this data structure encounter an update, those resources at different stages of access need to ensure it is "lock" safe when pointing to specific reference of that data structure.

Anyone encountering errors that mention rcu_sched can refer to kernel.org

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

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

</VirtualHost>

Step 2: Restart wen server

systemctl restart httpd

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