Initializing Postgresql server after a fresh install

These brief instructions are from Fedora 38.

Initialize the database

Initialize the database with this command (as root):

/usr/bin/postgresql-setup --initdb

Enable the service and start it

Run these systemctl commands (as root):
systemctl enable postgresql
systemctl start postgresql

Allow local trust for unix and IP4 sockets

Edit the /var/lib/pgsql/data/pg_hba.conf file as root and change the local line to trust:
local all all trust
host all all 127.0.0.1/32 trust

Then, restart the service.

Login as user postgres and create other users and databases

psql -U postgres
create role [user]
create database [user]
alter role [user] with login

Creating an SSL Certificate Signing Request

To create a CSR, use the openssl command.

openssl req -nodes -new -keyout server.key -out server.cert

The -nodes option tells openssl not to use DES to encrypt the key.


For a self-signed certificate: openssl req -nodes -new -x509 -keyout server.key

Comparing two Postgresql tables

To find differences between two tables with the same columns, use the EXCEPT keyword. This can be extended to as many columns as are in the tables.

SELECT id, employee_id, effective_on, 'not in table2' AS note FROM table1 EXCEPT SELECT id, employee_id, effective_on, 'not in table2' AS note FROM table2;

Online resizing of an LVM logical volume on Linux under VMWare

Layer upon layer of abstraction can complicate this procedure. The common case is that a logical volume is running out of space and needs to be expanded. This can all be done non-destructively and while the volume is in use. It can be expanded by adding a SCSI disk within VMWare or by increasing the size of an existing SCSI disk in VMware. There may be some SAN level commands required first, and those will vary by the kind of SAN connected to VMWare. This specific recipe is based on first increasing the size of an existing SCSI disk in VMWare.

Step One - tell Linux to check the size of the SCSI disk that was expanded in VMWare.

Initially, Linux will not see any changes in the disk configuration. To see all the SCSI devices the system knows about, run:
ls /sys/class/scsi_device/
0:0:0:0 0:0:1:0 0:0:2:0

Once the expanded disk is identified, enable a rescan on the device:
echo 1 > /sys/class/scsi_device/0\:0\:2\:0/device/rescan

Then, run pvdisplay to make sure the physical volume shows the new size. Also, note in the output which volume group the physical volume is assigned to for the next step.

Step Two - make the sure the volume group shows free space

vgdisplay volume-group-name

Check to verify the volume group now has free space.

Step Three - expand the logical volume

Run the following command (replacing the logical-volume with the correct name) to expand the logical volume using ALL available free space in the volume group. If that's not what you want, check the syntax of lvexpand to add the amount of space you want to use.

lvextend -l +100%FREE logical-volume

Step Four - expand the Linux filesystem

Ext3/4 and XFS filesystems can be expanded

resize2fs /dev/mapper/vg-name/lv-name
OR
xfs_growfs /dev/mapper/vg-name/lv-name

Dumping PDF form fields in Linux

To see form fields in a fillable PDF:
pdftk pdf-file dump_data_fields

Note the operator (dump_data_fields) comes after the PDF file name.

How to dump your Ipod

A long time ago, in a galaxy nearby, I bought into the Apple-Unix fusion. Apple scrapped their weak OS and gave birth to OS X, essentially FreeBSD with a nice GUI. And it was good. Most of my Linux command line skills directly transferred, and I could manage Macs as easily as Red Hat boxen. But then time happened. Services started collapsing into Apple proprietary services. Config files and dot files morphed into XML registries with binary fields. Apple started fixing Unix until it was broken. I got off the bandwagon.

I always kept my data in neutral formats. Text, PDF, mp3. It was easy for me to transfer my data to a new system and pick up new programs, or crusty old programs. The one clinger was an ipod nano. I used my ipod in my Toyota mainly to run playlists and be able to play music when I was out of service for streaming. I spend a lot of time in the back country so being out of service is a common thing. Despite my disillusionment with Tim Cook and his wonder watch, the ipod was and is a state of the art music player. Itunes on the other hand, followed the same degrading path as OS X, becoming less usable, unfriendly, and more buggy with each new version. This became a real problem when the 5 year old ipod started to fail. Rebuilding it from scratch would not fix the issues with it and it was time to look for a new solution. Replacing that symbiotic ecosystem was not easy as I found out.

Where are all the ipod competitors? There are a few Chinese knock offs, but the big players have all given up. Microsoft dumped the Zune. Samsung stopped making their ipod competitor. A replacement required a hardware and software combo solution. After some trial and error, including trying one of the knock offs, I settled on Music Monkey (free) and a low profile USB flash drive. I bought a 32 GB SanDisk Cruzer Fit for $11.95 on Amazon.

I do development on Linux, but I have a Win10 machine for home use and game play. Media Monkey easily imported my music files and could dump them onto the Cruzer without problems. I had everything in a single flat directory. But things were not that simple. The Toyota music head (2015) would only index up to 999 files in a directory before giving up. This meant that even through my music was all on the USB flash drive, the car did not see it all. I went back and manually organized the files into directories by artist. This improved the situation, but the car created it's index not based on the physical layout (other than the 999 file restriction), but based on MP3 metadata tags. It was able to index more of the music but not everything. The work around for that was to clean up my metadata. Not an insignificant amount of work.

The final hurdle was to get playlists working. The car did not understand the standard playlist format from Media Monkey. What I did instead was use Genre. The Genre tag is so arbitrary that it is otherwise useless. When I want to change a playlist, I first create a true playlist in media monkey, select all the files and assign the playlist name to the Genre of all the files. Boom, done! A bonus hack is start all the Genre names with AA* so they all appear in the same place at the top of the genre index. For example, AABeatles for a Beatles playlist. It took some trial and error, but I have a better, cheaper solution for offline music and have abandoned the last vestiges of the company with the flying saucer HQ.

Exim cheatsheet

BradTheMad published this excellent reference for getting things done with Exim.

SSH

SSH Client

Common client files in the ~/.ssh/ directory

known_hosts => ip/hostname and fingerprints of servers that have been seen before
authorized_keys => concatenated list of public keys that can login as this user without a password

To generate a public/private RSA key pair (id_rsa and id_rsa.pub)

ssh-keygen -t rsa -b 4096 Private keys must be readable only by the user or SSH will ignore it for safety. Also, the .ssh directory must be readable only by the user.

To copy the public key to a server

ssh-copy-id -i id_rsa.pub user@server
After completion, make sure the key was appended to the .ssh/authorized_keys file on the server.

SSHd server

The config file is /etc/ssh/sshd_config

Best practice security settings

• On public facing servers, always disable root logins with PermitRootLogin no
• For extra security, specifically limit the users that can login with AllowUsers neo trinity
• Allow login via keys with AuthorizedKeysFile .ssh/authorized_keys
• You can change the port that SSH listens on, but a good port scanner will find it wherever it is

Pulling single tables out of a large MySQL dump file

This is a problem I run into occasionally when I need to restore only one or two tables from a large database dump file in text format.

It can be done with many scripting languages, but I found a nice awk command on the T-sheets blog. To use the awk command, you need to know the names and order of the tables in the dump file. First, grep for "CREATE TABLE" to find the names and order of tables in the dump:

grep -n "CREATE TABLE" dumpfile.sql

The -n switch adds the line number which is not really needed. Next, plug in the name of the table you want to extract and the name of the table immediately following it:

awk ‘/Table structure for table .table-to-extract./,/Table structure for table .table-after./{print}’ dumpfile.sql > /tmp/extracted_table.sql

Next, I usually go in and add a "use my_database_name" command at the top and remove any unnecessary commands added by the mysqldump program. The last step is to feed the extracted table SQL to mysql.

Printing from Android Phones to Epson printers

The problem of printing from mobile devices is somewhat involved. The main problem is that standards like WiFi Direct or bluetooth printing are still in early stages of deployment. Some vendors have their own solutions that work pretty well with their devices, but they tend to be islands. Google has cloud print, but that requires a dedicated print server on the local network running headless Chrome to route the print jobs. I don't know the details of Apple Cloud Print, but it probably works in a similar way, with some local device acting as the print server.

On my Samsung Galaxy S6, I decided to download the Epson Print Enabler from the Play Store. Once it was installed and enabled, I took the phone to close proximity of my printer, and Epson WF-3520 multi-purpose wireless inkjet and tried to print a web page from Chrome. Chrome defaults to printing to a PDF document, but I was able to select the Epson from a drop down list, deselect the pages I did not want to print and send a single page of the web site to the printer. It worked like magic.

This is one of the island solutions that might only work with Android phones and Epson wireless printers, but it does work without any print server set up or routing through the Internet. The Epson printer is the best wireless inkjet I've owned and is highly recommended whether you want to use the Android printing features or not.

Limiting connections to port 80

The Linux software firewall, iptables, has the ability to limit the number of concurrent connections on a specific port. This could be used as a crude DDOS defense. It won't save a web site, but it might save the server from becoming overwhelmed and unresponsive. Here is an example of limiting the number of connections on port 80 to 25. After 25 open connections, the next connection is dropped.

iptables -A INPUT -p tcp --syn --dport 80 -m connlimit --connlimit-above 25 -j REJECT --reject-with tcp-reset

Simple routing in Linux

The Linux kernel has pretty capable network routing capabilities.

To see the current routing table:
route -n

The default gateway will have the "UG" flags shown in the output. Red Hat and CentOS systems usually have the routing table stored in /etc/sysconfig/network-scripts/ by network device (e.g., route-eth1, route-eno1). To change or modify the routing table, you must be root.

To add a default gateway from the command line:
/sbin/route add default gw ip-address eth0

To add a static route, use the ip command and specify the destination and interface:
/sbin/ip route add 192.168.1.0/24 via 192.168.2.254 dev eno1

To see how packets will be routed to an ip address:
/sbin/ip route get ip-address

Chromebook keyboard shortcuts

Last year, I replaced my Android tablet with an Acer i3 Chromebook. It was one of my best technology moves of the year. I love the form factor, keyboard, built-in SSH, screen, external ports, and ability to run a native Linux distro in paravirtual machine. The only thing I didn't like about the keyboard was that is was missing a DELETE key. The fix for that is a keyboard shortcut:
Alt+Backspace

Take a screenshot with Ctrl+[show windows].

The show windows button looks like a window with two vertical bars after it. Screenshots are saved to your Downloads directory with the date and time as PNG files.

Here are more keyboard shortcuts from OMG Chrome.

Ruby on Rails scaffold generated form fields

Rails has a cool feature that generates basic CRUD screens based on command line input. It can generate a model file, a set of views, a controller, tests, and migrations. While officially shunned for production code, scaffolding may produce most of the functionality you need in parts of your application.

It generates views with array style form field names. For example, if your model name is "widget" and it has an attribute of "column1", the form name generated in views will be name="widget[column1]". It may not be immediately clear how to reference this field using params in the controller.

The answer is to reference it as a hash of hashes. A simple name="field" is referenced in the controller as params[:field]. The scaffold generated field in the above example would be referenced as params[:widget][:column1].

Chromebook, crouton, Ubuntu trusty

I had been in the market for a low cost Linux laptop for a while, but was not overly excited about the choices. There are a small number of Linux laptop vendors, and Dell offers an Ubuntu based ultrabook at a price around $1300. That is not a low cost option.

Then, I found a blog post where someone had installed Linux on a chromebook. I always thought chromebooks were interesting, but limited. The ability to run Linux in a paravirtual mode sold me, but I still had issues with the performance, having used a chromebook loner earlier this year. I was waiting for the new generation of chromebooks to be released late this summer with Intel i3 processors. That would be plenty of horsepower to run Linux and Chromium at a low cost.

I am typing this post on an Acer C720 with the i3 and 4GB RAM ($379), just a few days out of the box. So far, I am happy with the fit and finish of the Acer, the performance, the ports (2 USB, 1 SD, 1 HDMI, Wifi, Bluetooth), and battery life. Following this guide from Lifehacker, using a script from Google called crouton, I installed Ubuntu in a chroot environment and am able to hot key back and forth between Linux and Chromium. It is freaking sweet!

If you run a crouton install with no parameters, you get Ubuntu 12 which is a couple of years old. However, there are 3 versions of Debian, a penetration testing distro called Kali, and 2 versions of Ubuntu LTS available from crouton. After some experimentation, I eventually installed Ubuntu 14 LTS (trusty) with the Unity desktop using this command:

sudo sh -e ~/Downloads/crouton -r trusty -t unity

It doesn't come with a lot of applications, so I need to get busy with apt-get and set up my development environment. This is the low cost Linux machine I've been wanting.

Blocking and unblocking an IP using iptables

Iptables is the Linux software firewall.

To block an IP (all ports), as root:

iptables -I INPUT -s ip-address -j DROP

To unblock an IP:

There are two steps. The iptables rule must be deleted by line number, so first you need to determine which rule you want to delete.
iptables -L -n --line-numbers

Next, delete the rule for the IP you want to unblock. This will delete rule number 3:
iptables -D INPUT 3

To clear all firewall rules, use the flush switch
iptables -F

Apache processes, process size, IP clients, and status

To help monitor apache performance, here are some useful command line recipes.

Show how many apache processes are running

ps aux | grep [h]ttpd | wc -l
The bracketed [h] prevents the grep process itself from being counted.

Show the average apache process size in MB

ps aux | grep [h]ttpd | awk '{print $6/1024;}' | awk '{avg += ($1 - avg) / NR;} END {print avg " MB";}'

Show the top 10 apache client IPs by number of sockets

/bin/netstat -ntp | /bin/awk '$4 ~/:(80|443)$/ {print $5}' | /bin/sed 's/.*ffff://' | /bin/cut -d: -f 1 | /bin/sort | /usr/bin/uniq -c | /bin/sort -nr | /usr/bin/head

Show full status

apachectl fullstatus

Adding will_paginate to a rails application

There are times when you have a lot of records to display and want to provide an easy way to navigate through the entire unfiltered list. Various paginate gems offer a solution. The one used most in my shop is will_paginate.

To add it to an application, the first obvious step is to install it if not already installed.
gem install will_paginate

Once available, add it to the config/environment.rb:
config.gem 'will_paginate', :version => '~> 2.3.16' (adjust for your version)

If you are using Bundler, add it to the Gemfile.

Next, add the paginate method to your controller result set for all records to be paginated:
@records = Record.find(:all)
@records = @records.paginate(:page => params[:page], :per_page => 30)

Finally, add code to your view to display the navigation. I sometimes add it to both top and bottom, depending on how many records I am displaying.
<%= will_paginate @records, :style => 'color:blue' %>

Mysql commands

Login to MySQL

mysql --user=xxx --password=xxx --database=xxx

Dump a database

mysqldump --user=xxx --password=xxx dbname

Dump a single table with data

mysqldump --user=xxx --password=xxx dbname tablename

Dump a single table with only some of the data using --where

mysqldump --user=xxx --password=xxx dbname tablename --where="db='blog3'"

Dump a single table with data in tab separated format (for import to XL)

mysqldump --user=xxx --password=xxx dbname tablename -T path
The -T path (example: -T /tmp) tells mysqldump where to create a tablename.sql file with the table definition, and a tablename.txt file with the tab separated data from the table.

Dump a single table structure without the data

mysqldump --user=xxx --password=xxx --no-data dbname tablename

Grant all privileges on a database to a user, be sure to flush privileges after to make the security change effective immediately

GRANT ALL ON db1.* TO 'username'@'localhost';
FLUSH PRIVILEGES;

Getting Rails variables into external JavaScript files

It is often convenient to keep JavaScript code in external .js files and pull them into a web page using this HTML command:

<script src="external.js"></script>

In a normal rails .erb or .haml view, you can't use a rails variable value inside the external JavaScript file. The trick is to set the value in an HTML meta tag, then use JavaScript to read the meta tag after the DOM is ready. For example, in the .erb view file, use:

<meta name='railsvariable' content='<%= @railsvar %>' /%>

Then, in the JavaScript file, using the Dojo query function:

var railsvar = query('meta[name="railsvariable"]')[0].content;

In raw JavaScript, it would be this:

var railsvar = document.getElementsByTagName('meta').item(property='railsvariable').getAttribute('content');