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 on irc I figured I can write a tool to automatically check these rules. Or at least – most of them.

Currently wiki lists contains 18 rules:

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 on irc.

Hope you'll find it useful.

2 thoughts on “Don’t do these things in PostgreSQL”

  2. @Alicja:

    Possibly, will think about it, as it’s not on the main don’t wiki, so handling would be slightly different.

Comments are closed.