Blog

Recently I’ve frequently been finding myself needing to look up details of custom stored procedures in PostgreSQL databases. Here are a few helpful queries for that.

First, a simple list of the queries:

SELECT n.nspname AS schema, p.proname AS procedure FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid WHERE n.nspname NOT IN ('pg_catalog', 'information_schema') AND p.prokind = 'p';

Second, the owner of the procedures:

SELECT proname, proowner::regrole FROM pg_proc WHERE pronamespace::regnamespace::text = 'public';

Google Workspace (formerly G Suite, formerly Google Apps for Business) is changing its default policy to require 2FA (two factor authentication) for all accounts. While this is good news for security, Google have made it very difficult to maintain privacy at the same time.

A couple of times recently I’ve seen a mysterious error while developing PHP applications using MariaDB. The error logs show something like the following:

Unable to execute INSERT INTO user ( email, password ) VALUES ( :email, :password ). Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'email' in 'where clause'.

For data heavy web projects with data displayed in tables, I frequently use the jQuery tablesorter plugin to allow users to easily sort data without writing a lot of custom code or relying on server-side processing. The plugin autodetects many data types, including dates, but I wasn’t entirely happy with the built-in date processing on a project I recently worked on.

Recently I was making a simple web form to allow admins of a site to create accounts for new users. The admin enters the email address of the new user. When the form is submitted, a random password is generated and emailed to the user. (This is for privacy/security: it prevents the admin from knowing another user’s password.) However, the admin needs to enter their own password when submitting the form. In other words, the email address and password fields in the form are for two different accounts.

Here’s the situation: your colleague is working on laptop C. They need to get into server A. Server A is behind a firewall, and can create outbound connections, but inbound connections are not allowed. You have access to an internet server B, but you don't want to allow full shell access on there to your colleague.

The TotoLink LR1200 is a fairly cheap WiFi access point and 4G/ethernet modem router available in Asia. It's also known as the Zioncom AC1200. It has two web interfaces: a standard interface with all the features available, and a simplified version designed for mobile devices.

By default, phpMyAdmin is missing a number of useful configuration options. When you install it from source, you can set these options by editing /path/to/phpmyadmin/config.inc.php. However, Debian and Ubuntu package up phpMyAdmin and store things in a slightly different place. This makes installation, update and maintenance much more convenient, but does make things slightly more confusing when you want to set custom options.

When reviewing code, a simple first step in checking that it doesn’t include any errors is to run a syntax check. In this post, I outline how to set up tools to scan all the code in a directory for syntax errors, and how to run this scan automatically when committing your code to git. For simplicity’s sake I am assuming that you are running Ubuntu, but the principles apply to any development environment.

PHP

The command line version of PHP (package php7.x-cli) allows you to syntax check a single file:

In this post, I describe how to set up spam filtering rules based on the country that an email was sent from, using SpamAssassin. These notes are based on Debian Buster, but the principles are very similar for other versions of Debian and Ubuntu. If you run a mail server that primarily only receives mail from a few countries, this guide should be very helpful to you in the fight against spam.