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:
- 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.
Hope you'll find it useful.