Postgres High-Level View

This information is directed at beginning users and was created for a student. The following section shows a scenario that applies to PostreSQL17 installed on a mac.

Sample CLI Database Creation

Place the following SQL commands in a file named setup.sql

-- SQL commands in setup.sql

-- Create a new database
CREATE DATABASE mydb;

-- Connect to the new database
\connect mydb

-- Create a table
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    hire_date DATE
);

-- Insert some sample data
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES
('John', 'Doe', 'john.doe@example.com', '2023-01-15'),
('Jane', 'Smith', 'jane.smith@example.com', '2023-02-10'),
('Alice', 'Johnson', 'alice.johnson@example.com', '2023-03-20');

-- Create another table
CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(100)
);

-- Insert data into the departments table
INSERT INTO departments (dept_name) VALUES
('Human Resources'),
('Engineering'),
('Marketing');

Then run the following command.

psql -h localhost -p 5432 -U postgres -f setup.sql
  • Connect with psql
  • select * from employees;

Commands

  • \l # list databases
  • \c <database> # connect to a database
  • \d <table> # describe a table
  • \d+ <table-name> # more information about a table
  • \dt # display tables
  • \dn # list all database schemas
  • \du # list users and their roles
  • \du <user> # retrieve a specific user
  • \df # list all functions
  • \q # quit postgres

Air Force Academy Quotes

Class of 1984, Contrails 1980-1981

The Purpose of the Fourth-Class System

The purpose of the Fourth-Class System at the United States Air Force Academy is to lay the foundation early in the cadet’s career for the development of those qualities of character and discipline which will be expected of an officer. These qualities must be so deeply instilled in the individual’s personality that no stress or strain will erase them.

Duty then is the sublimest word in the English language. You should do your duty in all things. You can never do more. You should never wish to do less.
-General Robert E. Lee

In the development of air power, one has to look ahead and not backward and figure out what is going to happen, not too much of what has happened.
-Brigadier General William “Billy” Mitchell; Winged Defense, 1924

Victory smiles upon those who anticipate the changes in the character of war, not upon those who wait to adapt themselves after the changes occur.
-Italian Air Marshall Guilio Douhet, 1928

I don’t mind being called tough, since I find in this racket it’s the tough guys who lead the survivors.
-Colonel Curtis Lemay, USA, to Lieutenant General Ira Eaker, USA, in England, 1943

When there is a visible enemy to fight in combat…many serve, all applaud and the tide of patriotism runs high. But when there is a long, slow struggle with no
immediate, visible foe, your choice will seem hard indeed.

-President John F. Kennedy, Address to the Graduating Class, U.S. Naval Academy, June 1961

Never tell people how to do things. Tell them what to do and they will surprise you with their ingenuity.
General George S. Patton, Jr., War as I Knew It.

If I can’t go back with my self-respect, I won’t go
back at all.

-Captain Harris, USAF, in solitary confinement for 14 months as a Prisoner of War in Korea.)

You may have to fight when there is no hope of victory, because it is better to perish than live as slaves.
-Sir Winston Churchill

Never in the field of human conflict was so much owed
by so many to so few.

-Sir Winston Churchill, after the Battle of Britain.

If you can ‘t get them to salute when they should salute
and wear the clothes you tell them to wear, how are you
going to get them to die for their country?

-General George S. Patton, Jr.

Man’s flight through life is sustained by the of
his knowledge.

-Inscription on the Eagle and The Fledglings by Austin “Dusty” Miller, HQ ATC.

There is no type of human endeavor where it important that the leader understands all phases job as that of the profession of arms.
-Major General James C. Fry

A leader is a man who has the ability to get other people to do what they don’t want to do, and like it.
-President Harry S. Truman; Memoirs, 1955

Be an example to your men, in your duty and in private life. Never spare yourself, and let the troops see that you don’t in your endurance of fatigue and privation. Always be tactful and well mannered and teach your subordinates to be the same. Avoid excessive sharpness or harshness of voice, which usually indicates the man who has shortcomings of his own to hide.
-Field Marshall Erwin Rommel

The discipline which makes the soldiers of a free country reliable in battle is not to be gained by harsh or tyrannical treatment. On the contrary, such treatment is far more likely to destroy than to make an army. It is possible to impart instruction and give commands in such a manner and such a tone of voice as to inspire in the soldier no feeling but an intense desire to obey, while the opposite manner and tone of voice cannot fail to excite strong resentment and a desire to disobey. The one mode or the other of dealing with subordinates springs from a corresponding spirit in the breast of the commander. He who feels the respect which is due to others cannot fail to inspire in them respect for himself while he who feels, and hence manifests disrespect toward others, especially his subordinates, cannot fail to inspire hatred against himself.
-Major General John M. Scholfield’s graduation address to the graduating class of 1879 at West Point

The general is sorry to be informed that the foolish and wicked practice of profane cursing and swearing (a vice heretofore little known in an American army) is growing into fashion; he hopes that the officers will by example as well as influence, endeavor to check it, and that both they and the men will reflect that we can have little hope of blessing of heaven on our arms if we continue to insult it by our impiety and folly. Added to this, it is a vice so mean and low that every man of sense and character detests and despises it.
-General George Washington: General Order to the Continental Army, 3 August 1776


The Keeping of Light

gRj

I still believe—
not in the lies they wore like perfume,
but in the way my hands,
though scarred,
still cup hope like a match
in a windstorm.

Yes, I love too loud.
Yes, I trust the map
before I check for cliffs.
But this is not weakness—
it is the stubborn art
of planting gardens
in a world that prays for rain.

Let them call me reckless.
Let them say I burn too bright
for my own good.
I have known the heat of a thousand false suns
and still, my skin remembers
how to glow.

One day, a woman will stay
as true as her first laugh.
One day, the love I give
will not be a question
but an echo returned
in the same language.

Until then, I practice
the sacred act of falling
and rising—
not as a fool,
but as a tide
that knows its own worth
even when the moon lies.

The Letting Go

gRj

At first, I am stone—
all edges and warnings,
a cliff face carved with no and not yet.
But you? You arrive like weather:
soft hands, softer laughter,
a promise of sun after drought.

(And I forget.
I always forget.)

The days stack like kindling.
Your voice becomes the flint.
I burn so slow I don’t notice
the fire until my skin
smells like sacrifice.

Here is where you change:
The jokes grow teeth.
The silences thicken.
Your love, once a lantern,
now a flickering conditional.

I stay too long—
not because I’m weak,
but because I keep digging
for the woman I met
in the ashes of the one you became.

The leaving is never clean.
It is a bone reset without anesthetic,
a name scraped from my ribs.
But I do it.
Again and again,
I do it.

And if my heart is a map
of all the exits I ignored,
at least I finally learn
to walk through them.

Auto-Discovery with NMAP

June 26th, 2017 | Garland Joseph

I created this script in order to use it as a feed for enterprise management tools such as Nagios.   It’s a bit difficult to get a handle on auto-discovery tools within an enterprise management tool when it discovers what can be an overwhelming number of hosts.  NMAP is smart enough to translate the MAC address into a vendor if it can.

This script called discover uses the NMAP tool on Linux (tested on Centos).


#! /bin/bash
#
# Name: discover
#
# Garland Joseph, garland.joseph@gmail.com
# Date: June 2017
#
# Auto-discover on subnet using nmap, can be fed into something like nagios
# as a seed file after proper formatting.
#
# ----

if [[ -z ${1} ]]
then
cat <<EOD
$0 <subnet>
where
subnet, by example, is something like 192.168.1.0/24
EOD
exit
fi

nmap -sn ${1} | awk '
BEGIN{ printf("%-16s| %-18s| %-35s| %-30s\n","IP","MAC","NAME","VENDOR") }
/^Nmap scan report/ {
NAME=$5
x=$NF
gsub("[()]","",x)
IP=x
}

/^MAC Address/ {
MAC=$3
split($0,a,"(")
split(a[2],b,")")
VENDOR=b[1]
printf("%-16s| %-18s| %-35s| %-30s\n",IP,MAC,NAME,VENDOR)
}'

Here is an example of the output
[root@localhost ~]# ./discover 192.168.1.0/24
IP            | MAC               | NAME                               | VENDOR
192.168.1.1   | C8:D7:19:DE:54:2E | NyaRaePrimary                      | Cisco Consumer Products
192.168.1.100 | B8:27:EB:72:2A:4A | kodi1.grandenetworks.net           | Raspberry Pi Foundation
192.168.1.101 | 00:1F:3B:75:7F:EB | 192.168.1.101                      | Intel Corporate
192.168.1.102 | 6C:3B:E5:76:96:A5 | HP-Printer.grandenetworks.net      | Hewlett Packard
192.168.1.105 | A8:47:4A:AC:8F:89 | 192.168.1.105                      | Unknown
192.168.1.106 | F0:7D:68:0A:7C:8A | OllieMaeJoseph.grandenetworks.net  | D-Link
192.168.1.107 | 58:82:A8:81:C3:A6 | XboxOne                            | Unknown
192.168.1.109 | 28:56:5A:39:ED:FD | BRW28565A39EDFD.grandenetworks.net | Unknown
192.168.1.110 | 64:20:0C:90:24:D9 | Garlands-iPad.grandenetworks.net   | Apple
192.168.1.112 | 7C:D1:C3:17:0C:58 | Apple-TV.grandenetworks.net        | Apple
192.168.1.115 | B8:27:EB:12:DA:AC | kodi2.grandenetworks.net           | Raspberry Pi Foundation
192.168.1.118 | A4:77:33:8E:CE:C2 | Chromecast.grandenetworks.net      | Google
192.168.1.119 | 6C:AD:F8:5D:3A:D6 | 192.168.1.119                      | Azurewave Technologies
192.168.1.131 | F0:7D:68:0A:7A:D5 | EmmaEdwards.grandenetworks.net     | D-Link
192.168.1.135 | 28:10:7B:0C:3A:71 | EarlEdwards.grandenetworks.net     | D-Link International
192.168.1.136 | 00:09:B0:D6:A8:2A | 192.168.1.136                      | Onkyo
192.168.1.138 | 28:10:7B:0C:3A:74 | LeeJoseph.grandenetworks.net       | D-Link International
192.168.1.145 | D4:3D:7E:EF:93:99 | obama                              | Micro-Star Int'l Co
192.168.1.147 | E4:3E:D7:44:21:8F | LGwebOSTV.grandenetworks.net       | Unknown

Synchronized Windows to Windows Packet Capture

June 13th, 2017 | Garland Joseph

This script is part of a series of scripts that perform packet capture between two endpoints.  In this case, the endpoints are two windows machines.  This script was tested with the “source endpoint” as a Windows 10 machine and the “target endpoint” a Windows 2016 Server machine.   The circular traces are started on each machine and stopped whenever an event is detected.  In this case the event is to monitor a file for a particular string.

Requirements: Wireshark installed on Windows
Caveats: Target endpoint must be Windows Server (Source endpoint can be windows client).

Scenario

Script

<#
################################################################################################

.SYNOPSIS
Author: Garland R.Joseph, garland.joseph@gmail.com, July 2016

Simple Packet capture from windows to windows based on monitoring a file.

.DESCRIPTION

Side-effects
Uses an interim x.x and local_trace.ps1 file, but removes them at end.

.EXAMPLE

w2wcap [ -verbose | -v ] [ -c "capture-file" ] [ -s "seconds" ] [ -u "remote user" ] [ -p "password" ]

-r "remote host" -l "log file" -t "text string" -a "local interface" -b "remote interface"

.PARAMETER v | verbose
Optional.  This option prints additional informational messages during processing.

.PARAMETER c
Optional. This option specifies the name of the capture file.  The default is set to capture.  The script is set to only create 2 files per endpoint and the default size is 512 MB. So the 2 files total will be less than a 1 GB.  You can modify this behavior by changing the FILESIZE and FILECOUNT variables below.  The filenames will look similar to...
capture_00049_20170612214134
capture_00050_20170612215042

.PARAMETER s
Optional. Range is 1 second to 18000 seconds (5 hours) for parsing the log file. The default is 5 seconds. Please examine the process to gauge impact on system resources.  The lower the number, the more system resources are consumed.

.PARAMETER u
Optional. This is the username for the remote user on the target endpoint. You can hard code the value in the script (see the $u=Wireshark line below) or optionally specify the name on the command line.  The account should have 
sufficient privilege to run the tshark command tool for the Wireshark facility. This parameter is optional, but recommended so you don't store the username in a file.

.PARAMETER p
Optional.  This is the password for the remote user.  This parameter is optional, but recommended so you don't store the password in a file.

.PARAMETER r
Required.  This is the hostname or ip address of the target endpoint.

.PARAMETER l
Required.  This is the log file that will be parsed for the string as specified by parameter t.  The traces will stop once a new string specified by parameter t is found in the log file.  The script parses for an initial count so the log file does not have to be "zeroed-out".

.PARAMETER t
Required.  This the text string used in parsing the log file.

.PARAMETER a
Required.  This is the ip address of the local interface on the source
endpoint that "talks" to the target endpoint.

.PARAMETER b
Required.  This is the ip address of the remote interface on the target
endpoint.
###############################################################################################
#>

#
# Setup Command Line Options
#

[CmdletBinding()]

Param (

[string]$c = "capture",

[ValidateRange(1,18000)]
[int]$s = 5,

[string]$u = "Wireshark",
[string]$p = "Pr1nceH1ll",


[Parameter(Mandatory=$True)]
[string]$r,

[Parameter(Mandatory=$True)]
[string]$l,

[Parameter(Mandatory=$True)]
[string]$t,

[Parameter(Mandatory=$True)]
[int]$a,

[Parameter(Mandatory=$False)]
[int]$b
)

If (-Not (Test-Path $l ) ) {
Write-Host "Log file $l does not exist"
exit
}

#
# Default options, can be changed to increase capture file size, count, etc
#


#$FILESIZE=1000 #units or kB, so this means 1 Meg
$FILESIZE=500000 #512 Meg
#$FILESIZE=1000000 #units or kB, so this means 1 Gig

$FILECOUNT=2 #creates a count of FILECOUNT of trace files at most of size FILESIZE

$TSHARK_LOCATION_REMOTE="c:\progra~1\wireshark\tshark"
$TSHARK_LOCATION_LOCAL="c:\progra~1\wireshark\tshark"
$TRACECMD_REMOTE="$TSHARK_LOCATION_REMOTE -b filesize:$FILESIZE -b files:$FILECOUNT -w $c -i $b"
$TRACECMD_LOCAL="$TSHARK_LOCATION_REMOTE -b filesize:$FILESIZE -b files:$FILECOUNT -w $c -i $a"

#
# Set up script block in order to evaluate parameters for remote command
# We use an interim file for asynchronously running the local trace
#

$sb_remote = {
param ($p1,$p2,$p3,$p4,$p5,$p6)
winrs /r:$p1 /u:$p2 /p:$p3 $p4
}

$TRACECMD_LOCAL &gt; ./local_trace.ps1

#
# Start trace on remote host, then on local host
#

Write-Verbose "Starting remote trace with ${TRACECMD_REMOTE}"
Write-Verbose "(winrs /r:$r /u:$u /p:$p $TRACECMD_REMOTE)"
Start-Job -Scriptblock $sb_remote -ArgumentList $r,$u,$p,$TRACECMD_REMOTE

Write-Verbose "Starting local trace with ${TRACECMD_LOCAL}"
Start-Process powershell.exe -ArgumentList "-file ./local_trace.ps1"

#
# Monitor log file
#
#
Write-Verbose "Start of monitoring file $l ever $s seconds for string $t..."
#init counters
Select-string -path "$l" -pattern $t | Measure-object -line | ft -hidetableheaders &gt; x.x ; $old_count = cat x.x | where {$_ -ne ""} | %{$_ -replace '\s+','' }
$new_count=$old_count
#loop until match found
$i=0
while ($new_count -eq $old_count) {
$i++
write-verbose "Iteration: $i, sleeping $s seconds..."
start-sleep -s $s
Write-verbose "Searching $l for $t..."
Select-string -path "$l" -pattern $t | Measure-object -line | ft -hidetableheaders &gt; x.x ; $new_count = cat x.x | where {$_ -ne ""} | %{$_ -replace '\s+','' }
}

#
# At this point, search string has been found, stop traces
#

#remote
taskkill /f /s $r /u $u /p $p /fi "imagename eq tshark*"

#local
taskkill /f /fi "imagename eq tshark*"

Write-Verbose "Traces completed after $i iterations. Examine the set of $FILECOUNT files on each endpoint with file name: $l on both endpoints."

#
# Clean up
#

if (Test-Path "./x.x") { rm "./x.x" }
if (Test-Path "./local_trace.ps1") { rm ./local_trace.ps1 }

exit

Synchronized Windows to Linux Packet Capture

July 5th, 2017 | Garland Joseph

This script is part of a series of scripts that perform packet capture between two endpoints.  In this case, the endpoints are a Unix machine and a windows machine. This script was tested with the “source endpoint” as a Redhat Linux and the “target endpoint” a Windows 2016 Server machine.  

The circular traces are started on each machine and stopped whenever an event is detected on the Unix side.  In this case the event is to monitor a file (i.e., log) for a particular string.

Requirements: Wireshark installed on Windows. OpenSSH installed on Windows. 

Scenario

Unix to Windows Capture Scenario

Script

#Author: Garland R. Joseph, garland.joseph@gmail.com
# Date: May 2017
# u2wcap: See usage below. "Unix to Windows Capture"
#
# This script is offered as is. It is designed to
# run a circular trace using tcpdump on UNIX system
# and wireshark on Windows systems.
#
# You will either have to manually enter the password
# for the root account on the remote system or setup
# ssh keys from promptless access.
#
# The traces will stop once a key string SEARCH_STRING is
# found in LOG_FILE.
#
# Note: Some UNIX systems like LINUX Fedora will
# result in permsission denied when using
# tcpdump -W and -C options and writing to / or /root.
#
# Modify the REM_INTERFACE parameter below to fix the interface number
# on the windows system. Do a tshark -D to determine the interface number.
#
#
# -----

#
# Defaults
#

USAGE="u2wcap [-v] [ -c capture_file ] [ -w secs ] -h remote_host -l log_file -s search_string"
DEBUG=false
SLEEP_TIME="5" #seconds
LOCAL_CAPTURE_FILE="/tmp/capture"
TCPDUMPCMD="tcpdump -C 1 -W 2 -w ${LOCAL_CAPTURE_FILE}"

#
# Options for remote tracing
#

REM_CAP_FILE="capture.windows"
REM_USER="wireshark"
REM_INTERFACE="4"
FILESIZE=1000 #units or kB, so this means 1 Meg
#FILESIZE=500000 #512 Meg
#$FILESIZE=1000000 #units or kB, so this means 1 Gig
FILECOUNT="2" #creates a count of FILECOUNT of trace files at most of size FILESIZE
TSHARK_LOCATION="c:\progra~1\wireshark\tshark"
#TRACECMD="$TSHARK_LOCATION -b filesize:$FILESIZE -b files:$FILECOUNT -w ${REM_CAP_FILE}"
TRACECMD="$TSHARK_LOCATION -b filesize:$FILESIZE -b files:$FILECOUNT -w ${REM_CAP_FILE} -i ${REM_INTERFACE}"


#
# Process command line arguments
#

while getopts ":vc:w:l:s:h:" opts
do
case ${opts} in
v) DEBUG=true ;;
c) CAPTURE_FILE=${OPTARG} ;;
w) SLEEP_TIME=${OPTARG} ;;
s) SEARCH_STRING=${OPTARG} ;;
l) LOG_FILE=${OPTARG} ;;
h) REMOTE_HOST=${OPTARG} ;;
":") echo "Please specify a value for ${OPTARG}" ; exit ;;
\?) echo "${OPTARG} is not a valid switch" ; echo "${USAGE}" ; exit;;
esac
done

#
# Insure required values have been specified, check for existence of
# log file, getops should handle case of no values for -l and -s.
# A sanity check in the event getopts varies per unix
#

if [[ -z ${SEARCH_STRING} || -z ${LOG_FILE} || -z ${REMOTE_HOST} ]]
then
echo ${USAGE}
exit
fi
if ! [[ -f ${LOG_FILE} ]]
then
echo "File ${LOG_FILE} does not exist"
exit
fi

#
# Start trace on remote host
#
$(ssh ${REM_USER}@${REMOTE_HOST} ${TRACECMD})& 2>&1 > /dev/null

#
# Start trace on this host
#

${TCPDUMPCMD} 2>/dev/null 1>/dev/null & LOCAL_PID=$!
${DEBUG} && echo "${0}-I-LOCAL_PID, local pid is ${LOCAL_PID}."

#
# Monitor log file
#

old_count=`grep -c ${SEARCH_STRING} ${LOG_FILE}`
(( new_count=old_count ))
(( i = 0 ))
while (( old_count == new_count ))
do
(( i++ ))
${DEBUG} && echo "${0}-F-SLEEP, sleeping ${SLEEP_TIME}, iternation ${i}."
sleep ${SLEEP_TIME}
new_count=`grep -c ${SEARCH_STRING} ${LOG_FILE}`
done

#
# At this point, search string has been found, stop traces
#

kill ${LOCAL_PID}
ssh ${REM_USER}@${REMOTE_HOST} taskkill /f /fi \"imagename eq tshark*\"

#
# Reminders
#

echo "Consult files ${REM_CAP_FILE} on remote host ${REMOTE_HOST} and ${LOC_CAP_FILE} on local host."

exit