PostgreSQL select data in JSON format

SELECT audit_log.id,

    audit_log.event_time,

    audit_log.client_addr,

    audit_log.application_name,

    audit_log.username,

    audit_log.table_name,

    audit_log.operation,

    audit_log.after_value -> 'exterior'::text AS after_value_in_json

   FROM audit.audit_log

  WHERE 1 = 1 

  AND (audit_log.after_value ->> 'exterior'::text) ~~ '%sCrIpT%'::text

  ORDER BY audit_log.event_time DESC;

/

  

SELECT 

id,

event_time,

client_addr,

application_name,

username,

table_name,

operation,

after_value -> 'exterior'as after_value_in_JSON

FROM audit.audit_log

where 1=1

and after_value ->> 'exterior' like '%sCrIpT%'

LIMIT 20;

/

PostgreSQL Audit with Trigger - "Value-based Auditing (VA)"

1/ Creating a table for Trigger Data:

-- SEQUENCE: audit.audit_log_id_seq

-- DROP SEQUENCE IF EXISTS audit.audit_log_id_seq;

CREATE SEQUENCE IF NOT EXISTS audit.audit_log_id_seq

    INCREMENT 1

    START 1

    MINVALUE 1

    MAXVALUE 9223372036854775807

    CACHE 1;

ALTER SEQUENCE audit.audit_log_id_seq OWNER TO postgres;

GRANT ALL ON SEQUENCE audit.audit_log_id_seq TO postgres;

/

-- Table: audit.audit_log

-- DROP TABLE IF EXISTS audit.audit_log;

CREATE TABLE IF NOT EXISTS audit.audit_log

(

    id bigint NOT NULL

DEFAULT nextval('audit.audit_log_id_seq'::regclass),

    event_time text COLLATE pg_catalog."default",

    client_addr inet,

    application_name text COLLATE pg_catalog."default",

    username text COLLATE pg_catalog."default",

    table_name text COLLATE pg_catalog."default",

    operation text COLLATE pg_catalog."default",

    before_value json,

    after_value json,

    CONSTRAINT audit_log_pkey PRIMARY KEY (id)

)

WITH (

    OIDS = FALSE

)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS audit.audit_log OWNER to postgres;

GRANT ALL ON TABLE audit.audit_log TO postgres;

------------------------------ 2/ Creating a Audit Trigger:

-- FUNCTION: audit.audit_trigger()

-- DROP FUNCTION IF EXISTS audit.audit_trigger();

CREATE OR REPLACE FUNCTION audit.audit_trigger()

    RETURNS trigger

    LANGUAGE 'plpgsql'

    COST 100

    VOLATILE NOT LEAKPROOF

AS $BODY$

DECLARE

    old_row json := NULL;

    new_row json := NULL;

BEGIN

    IF TG_OP IN ('UPDATE','DELETE') THEN

        old_row = row_to_json(OLD);

    END IF;

    IF TG_OP IN ('INSERT','UPDATE') THEN

        new_row = row_to_json(NEW);

    END IF;

    INSERT INTO audit.audit_log(

        client_addr,

        application_name,

        username,

        event_time,

        table_name,

        operation,

        before_value,

        after_value

    ) VALUES (

        inet_client_addr(),

        current_setting('application_name'),

        session_user,

        current_timestamp,

        TG_TABLE_SCHEMA ||  '.' || TG_TABLE_NAME,

        TG_OP,

        old_row,

        new_row

    );

    RETURN NEW;

END;

$BODY$;

ALTER FUNCTION audit.audit_trigger() OWNER TO postgres;

GRANT EXECUTE ON FUNCTION audit.audit_trigger() TO postgres;

------------------ 3/ Insert Trigger to Table "public.salons":

-- Trigger: audit_log__public_salons

-- DROP TRIGGER IF EXISTS audit_log__public_salons ON public.salons;

CREATE TRIGGER audit_log__public_salons

    AFTER INSERT OR DELETE OR UPDATE

    ON public.salons

    FOR EACH ROW

    EXECUTE PROCEDURE audit.audit_trigger();

------------------

4/ Result: SELECT * FROM audit.audit_log ORDER BY id ASC LIMIT 100

NGINX config for Websocket

###############
#Nginx Virtual Host Config:
#tiktok.local
server {
listen 80;
listen 443 ssl http2;
listen [::]:443 ssl http2;
server_name tiktok.local;

ssl_certificate /home/iadmin/Workspace/local-ssl/tiktok.local/tiktok.local+4.pem;

ssl_certificate_key /home/iadmin/Workspace/local-ssl/tiktok.local/tiktok.local+4-key.pem;

location /ws {
proxy_pass http://127.0.0.1:3000/ws;
proxy_http_version 1.1;
proxy_set_header Upgrade $http_upgrade;
proxy_set_header Connection 'upgrade';
proxy_set_header Host $host;
proxy_cache_bypass $http_upgrade;
}

location / {
proxy_set_header X-Forwarded-Host $host;
proxy_set_header X-Forwarded-Server $host;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_pass http://127.0.0.1:3000$request_uri;
}
}
###############

# REF:
# https://www.digitalocean.com/community/tutorials/how-to-deploy-a-react-application-with-nginx-on-ubuntu-20-04
# https://stackoverflow.com/questions/55688883/set-up-nginx-proxy-for-react-application
# https://wiki.matbao.net/kb/huong-dan-cai-dat-chung-chi-ssl-tren-nginx/
# https://stackoverflow.com/questions/63134170/create-react-app-code-changes-behind-nginx-reverse-proxy-not-reloading-in-browse

NodeJS ".env.local":

CHOKIDAR_USEPOLLING=true

WDS_SOCKET_PORT=443

RESULT:

How to generate PI (3.14) from the Gregory-Leibniz's formula

#!/usr/bin/env python3

# ---------------------------------------

# Project Name : PythonCheatSheet

# File Name    : Calculate-Pi-3-Gregory-Leibniz.py

# Created Date : 2023-02-02 12:46:25 UTC+7

# Last Modified: 2023-02-02 12:48:49 UTC+7

# ---------------------------------------

from pytictoc import TicToc

t = TicToc()  # create TicToc instance

t.tic()       # Start timer

print(f"================================")

# DO SOMETHING

# https://www.mathscareers.org.uk/calculating-pi/

# Gregory-Leibniz:

# Pi/4 = [1 - 1/3 + 1/5 - 1/7 + 1/9 - ...]

def my_pi(p_number: int) -> float:

    my_result = 0

    for i in range(1, n+1, 1):

        if i % 2 == 0:  # even

            my_result = my_result - 1/(2*i - 1)

        else:           # odd

            my_result = my_result + 1/(2*i - 1)

    return my_result*4

n = 1000000

print(f'my_pi({n}): {my_pi(n)}')

print(f"================================")

t.toc()       # Print elapsed time: "Elapsed time is <xxx> seconds."

# ---------------------------------------

# REF:

# https://mathshistory.st-andrews.ac.uk/HistTopics/1000_places/

# 3.14159265358979323846264338327950288419716939937510

# RESULT:

# WINDOWS 3900x:

# ================================

# my_pi(1.000.000): 3.1415916535897743

# ================================

# Elapsed time is 0.127023 seconds.

# Linux 3900x:

# ================================

# PI     : 3.1415916535897743

# ================================

# Elapsed time is 0.209464 seconds.

# ================================

# PI     : 3.1415916535897743

# ================================

# Elapsed time is 0.197258 seconds

# LINUX 5600g:

# n = 100  # 3.1315929035585537

# n = 500  # 3.139592655589785

# n = 700  # 3.1401640828900845

# n = 1.000  # 3.140592653839794

# n = 2.000  # 3.1410926536210413

# n = 3.000  # 3.1412593202657186

# n = 5.000  # 3.141392653591791    - 0.001042 seconds.

# n = 1.000.000   # 3.1415916535897743  - 0.074705 seconds.

# n = 10.000.000  # 3.1415925535897915  - 0.757600 seconds.

# n = 100.000.000 # 3.141592643589326   - 7.646685 seconds.

# n = 1.000.000.000     # 3.1415926525880504 - 81.178900 seconds.

# n = 1.000.000.000     # 3.1415926525880504 - 76.741032 seconds

# n = 10.000.000.000    # 3.141592653488346  - 937.967539 seconds.

[Ubuntu 20.04.LTS.x64] Install newest [Python 3.11.1]

HOW TO INSTALL NEWEST PYTHON  FROM SOURCECODE

#UBUNTU 20.04.LTS.x64
##########################
REF: https://docs.posit.co/resources/install-python-source/
Posit - Install Python From Source
##########################

sudo apt-get update
sudo apt-get install gdebi-core

sudo apt-get build-dep python
sudo apt-get install libffi-dev libgdbm-dev libsqlite3-dev libssl-dev \
zlib1g-dev



cd /opt/setup/
export PYTHON_VERSION=3.11.1
export PYTHON_MAJOR=3

curl -O https://www.python.org/ftp/python/${PYTHON_VERSION}/Python-${PYTHON_VERSION}.tgz
tar -xvzf Python-${PYTHON_VERSION}.tgz
cd Python-${PYTHON_VERSION}


./configure \
--prefix=/opt/python/${PYTHON_VERSION} \
--enable-shared \
--enable-optimizations \
--enable-ipv6 \
LDFLAGS=-Wl,-rpath=/opt/python/${PYTHON_VERSION}/lib,--disable-new-dtags

make
sudo make install


# Looking in links: /tmp/tmp1i8nsd3d
# Processing /tmp/tmp1i8nsd3d/setuptools-65.5.0-py3-none-any.whl
# Processing /tmp/tmp1i8nsd3d/pip-22.3.1-py3-none-any.whl
# Installing collected packages: setuptools, pip
# WARNING: The scripts pip3 and pip3.11 are installed in '/opt/python/3.11.1/bin' which is not on PATH.
# Consider adding this directory to PATH or, if you prefer to suppress this warning, use --no-warn-script-location.
# Successfully installed pip-22.3.1 setuptools-65.5.0
# WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv
# root@3900xl /opt/setup/Python-3.11.1#






# Install pip into the version of Python that you just installed:
curl -O https://bootstrap.pypa.io/get-pip.py
sudo /opt/python/${PYTHON_VERSION}/bin/python${PYTHON_MAJOR} get-pip.py


# Verify that Python is installed by running the following command:
/opt/python/${PYTHON_VERSION}/bin/python${PYTHON_MAJOR} --version


# (Optional) Add Python to the system PATH:
# PATH=/opt/python/<PYTHON-VERSION>/bin/:$PATH
PATH=/opt/python/3.11.1/bin/:$PATH

# #GLOBAL PATH VARIABLES:
# PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/opt/jdk/bin:/opt/script:/opt/lampp/bin:/usr/local/pgsql/bin:/opt/mariadb/bin:/opt/python/3.11.1/bin
# export PATH

[Python] Nesting LIST inside DICT / Nesting DICT inside LIST / Nesting DICT inside DICT

#!/usr/bin/env python3

# ---------------------------------------

# Project Name: PythonCheatSheet

# File Name: Dict15-01-Nested-Dict.py

# Created Date : 2022-12-31 08:56:51 UTC+7

# Last Modified: 2022-12-31 09:09:29 UTC+7

# ---------------------------------------

import json

from pytictoc import TicToc

t = TicToc()  # create TicToc instance

t.tic()       # Start timer

print(f"================================")

print(f'---------------------')

print(f'# Nesting LIST inside DICT:')

programming_language = {

    'Elshad': ['Python', 'Java', 'C#'],

    'Renad': 'Scratch',

    'Edy': 'Java',

}

print(f'programming_language:')

print(json.dumps(programming_language,

                 sort_keys=False,

                 indent=4,

                 separators=(',', ': ')

                 ))

print(f'\n---------------------')

print(f'# Nesting DICT inside LIST:')

programming_language = [

    {

        'user_name': 'Elshad',

        'favorite_language': ['Python', 'Java', 'C#'],

        'experience': 10,

    },

    {

        'user_name': 'Renad',

        'favorite_language': ['Scratch', 'Python'],

        'experience': 2

    },

]

print(json.dumps(programming_language,

                 sort_keys=False,

                 indent=4,

                 separators=(',', ': ')

                 ))

print(f'\n---------------------')

print(f'# Nesting DICT inside DICT:')

programming_language = {

    'Elshad': {

        'favorite_language': ['Python', 'Java', 'C#'],

        'experience': 10,

    },

    'Renad': {

        'favorite_language': ['Scratch', 'Python'],

        'experience': 2,

    }

}

print(f'programming_language:')

print(json.dumps(programming_language,

                 sort_keys=False,

                 indent=4,

                 separators=(',', ': ')

                 ))

print(f"================================")

t.toc() # Print elapsed time: "Elapsed time is <xxx> seconds."

# RESULT:

"""

# ================================

# ---------------------

# # Nesting LIST inside DICT:

# programming_language:

# {

#     "Elshad": [

#         "Python",

#         "Java",

#         "C#"

#     ],

#     "Renad": "Scratch",

#     "Edy": "Java"

# }

# ---------------------

# # Nesting DICT inside LIST:

# [

#     {

#         "user_name": "Elshad",

#         "favorite_language": [

#             "Python",

#             "Java",

#             "C#"

#         ],

#         "experience": 10

#     },

#     {

#         "user_name": "Renad",

#         "favorite_language": [

#             "Scratch",

#             "Python"

#         ],

#         "experience": 2

#     }

# ]

# ---------------------

# # Nesting DICT inside DICT:

# programming_language:

# {

#     "Elshad": {

#         "favorite_language": [

#             "Python",

#             "Java",

#             "C#"

#         ],

#         "experience": 10

#     },

#     "Renad": {

#         "favorite_language": [

#             "Scratch",

#             "Python"

#         ],

#         "experience": 2

#     }

# }

# ================================

# Elapsed time is 0.003771 seconds.

"""

OpenSSL Generate SSL CERT FOR IIS (PFX) from PEM

#!/bin/bash
#/etc/haproxy/certs/ssl-convert-PEM-2-PFX.sh
###################################################
#LastUpdate: #7:59 2022.02.18
###################################################
#__________[GLOBAL_VAR]:BEGIN
Domain_Name="example.com"

SSL_CRT_From_Provider="star_example.com.crt"
SSL_CA_From_Provider="DigiCertCA.crt"
SSL_CA_Bundle_Provider="My_CA_Bundle.crt"
SSL_CERT_PRIVATE="003-1-STAR_.example.com-PRIVATE.key"

SSL_CERT_PEM_FORMAT="$Domain_Name-wildcard-2021.10.03.pem"
#__________[GLOBAL_VAR]:END

openssl \
pkcs12 \
-inkey $SSL_CERT_PRIVATE \
-in $SSL_CRT_From_Provider \
-certfile $SSL_CA_Bundle_Provider \
-export -out $Domain_Name-wildcard-2021.10.03.pfx

echo "VERIFY SSL CERT:"

openssl pkcs12 -info -in $Domain_Name-wildcard-2021.10.03.pfx

#THE_END
#RESULT: example.com-wildcard-2021.10.03.pfx





REF:
1:
#https://www.sslshopper.com/ssl-converter.html
#Convert PEM to PFX:
#openssl pkcs12 -export -out certificate.pfx -inkey privateKey.key -in certificate.crt -certfile CACert.crt

2:
STEP BY STEP USING LETSENCRYPT TO MAKE SSL CERT FOR IIS - MANUAL