Hubert 'depesz' Lubaczewski: Don’t do these things in PostgreSQL

In 2018 Lluad created wiki page with couple of rules about what not to do .

Since then there have been 30 changes, done by nine more people.

Based on some conversation onirc I figured I can write a tool to automatically check these rules. Or at least – most of them.

Currently wiki lists contains 18 rules:

• Don't use SQL_ASCII
• Don't use psql -W or –password
• Don't use rules
• Don't use table inheritance
• Don't use NOT IN
• Don't use upper case table or column names
• Don't use BETWEEN (especially with timestamps)
• Don't use timestamp (without time zone)
• Don't use timestamp (without time zone) to store UTC times
• Don't use timetz
• Don't use CURRENT_TIME
• Don't use timestamp(0) or timestamptz(0)
• Don't use char(n)
• Don't use char(n) even for fixed-length identifiers
• Don't use varchar(n) by default
• Don't use money
• Don't use serial
• Don't use trust authentication over TCP/IP (host, hostssl)

Not all of them can be checked from within database, using SQL queries (for example, Don't use psql -W or –password ). But most can.

So pgWikiDont project was born. To run it, you generally need only to download it, cd to its directory, and run

=$ psql -f pgWikiDont.sql

Output can look like this:

pgWikiDont version 0.2 
 
Your database violates 2 rule(s):
=================================
 
Rule: Don't use money
  details: https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_money
  You have 1 column(s) that use money datatype:
      - public.z.x
 
Rule: Don't use trust authentication over TCP/IP (host, hostssl)
  details: https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_trust_authentication_over_TCP.2FIP_.28host.2C_hostssl.29
  You have 4 rule(s) in pg_hba.conf that use TRUST over TCP/IP:
      - host    replication all         127.0.0.1/32          trust
      - host    replication all         ::1/128               trust
      - host    all         all         127.0.0.1/32          trust
      - host    all         all         ::1/128               trust
 
 
 WARNINGS: 
===========
- You don't seem to have pg_stat_statements enabled, so queries can't be checked.

or like this:

pgWikiDont version 0.2 
 
Looks that your database is clean. Congratulations.
===================================================
 
 
 WARNINGS: 
===========
- You don't seem to have pg_stat_statements enabled, so queries can't be checked.
- Can't check for TRUST authentication because your hba file (/etc/postgresql/11/main/pg_hba.conf) is outside of data_directory (/var/lib/postgresql/11/main).

or, hopefully, simply:

pgWikiDont version 0.2 
 
Looks that your database is clean. Congratulations.
===================================================

It doesn't require any special programs or tools – just psql and installed pl/PgSQL language.

I tried hard to make it work on all supported PostgreSQL versions, but bugs can happen. If anything bad would happen, please create issue , or simply reach me onirc.

Hope you'll find it useful.