The list of all the queries

Password should not be open text

Query goal: Find base table columns that name refers to the possibility that these are used to register passwords. Find the columns that have a CHECK constraint that seems to determine the minimal or maximal permitted length of the values in the column. Passwords in a database table must be hashed and salted. Checking the strength of the password by using a check constraint is in this case impossible and the check constraints that try to do it should be removed from the database.
Notes about the query: The query takes into account check constraints that are associated with the table directly as well as constraints that are associated with it through a domain. The query takes into account only check constraints that are associated with one column. However, it could be that a check of values in the password columns is a task of a larger multi-column CHECK constraint. The query considers both column names in English and Estonian.
Query type: Problem detection (Each row in the result could represent a flaw in the design)
Query reliability: Low (Many false-positive results)
Query license: MIT License
Fixing suggestion: Drop the unnecessary constraints.
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

WITH pwd_domains AS (
SELECT cdu.table_schema, cdu.table_name, cdu.column_name,
cc.check_clause
FROM ((INFORMATION_SCHEMA.column_domain_usage AS cdu INNER JOIN
INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)) INNER JOIN INFORMATION_SCHEMA.domain_constraints AS dc
USING (domain_schema, domain_name)) INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc
USING (constraint_catalog, constraint_schema, constraint_name)
WHERE t.table_type='BASE TABLE' AND cdu.column_name~*'(parool|salasona|passw*|pwd)' AND
cc.check_clause NOT LIKE '%IS NOT NULL'  AND 
cdu.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)),

pwd_tables AS (SELECT 
n.nspname AS table_schema,
c.relname as table_name, 
a.attname AS column_name,
substring(pg_get_constraintdef(o.oid),7) AS check_clause
FROM pg_constraint o INNER JOIN pg_class c ON c.oid = o.conrelid
INNER JOIN pg_namespace n ON n.oid=c.relnamespace
INNER JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = o.conkey[1] AND a.attisdropped = FALSE
WHERE cardinality(o.conkey)=1 AND o.contype = 'c' AND
a.attname~*'(parool|salasona|passw*|pwd)'
AND n.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)),

pwd AS (SELECT table_schema, table_name, column_name, check_clause
FROM pwd_tables
UNION SELECT table_schema, table_name, column_name, check_clause
FROM pwd_domains)

SELECT table_schema, table_name, column_name, check_clause
FROM pwd
WHERE (check_clause~'(>|<)' AND check_clause!~'btrim[(].+[)][[:space:]]*<>' AND check_clause!~'[[:space:]]*<>[[:space:]]*['']{2}') OR
(check_clause~'~.+(\{[[:digit:]]+,\}|\{,[[:digit:]]+\})') 
ORDER BY table_schema, table_name ;

SQL statements for generating SQL statements that help us to fix the problem

SQL queryDescription
WITH pwd_tables AS (SELECT 
o.conname AS constraint_name,
n.nspname AS table_schema,
c.relname as table_name, 
substring(pg_get_constraintdef(o.oid),7) AS check_clause
FROM pg_constraint o INNER JOIN pg_class c ON c.oid = o.conrelid
INNER JOIN pg_namespace n ON n.oid=c.relnamespace
INNER JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = o.conkey[1] AND a.attisdropped = FALSE
WHERE cardinality(o.conkey)=1 AND o.contype = 'c' AND
a.attname~*'(parool|salasona|passw*|pwd)'
AND n.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL))
SELECT format('ALTER TABLE %1$I.%2$I DROP CONSTRAINT %3$I;', table_schema, table_name, constraint_name) AS statements
FROM pwd_tables
WHERE (check_clause~'(>|<)' AND check_clause!~'btrim[(].+[)][[:space:]]*<>' AND check_clause!~'[[:space:]]*<>[[:space:]]*['']{2}') OR
(check_clause~'~.+(\{[[:digit:]]+,\}|\{,[[:digit:]]+\})') 
ORDER BY table_schema, table_name ;
Drop the constraint that is associated directly with the table.
WITH pwd_domains AS (
SELECT cdu.domain_schema, cdu.domain_name, dc.constraint_name, cc.check_clause
FROM ((INFORMATION_SCHEMA.column_domain_usage AS cdu INNER JOIN
INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)) INNER JOIN INFORMATION_SCHEMA.domain_constraints AS dc
USING (domain_schema, domain_name)) INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc
USING (constraint_catalog, constraint_schema, constraint_name)
WHERE t.table_type='BASE TABLE' AND cdu.column_name~*'(parool|salasona|passw*|pwd)' AND
cc.check_clause NOT LIKE '%IS NOT NULL'  AND 
cdu.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL))
SELECT format('ALTER DOMAIN %1$I.%2$I DROP CONSTRAINT %3$I;', domain_schema, domain_name, constraint_name) AS statements
FROM pwd_domains
WHERE (check_clause~'(>|<)' AND check_clause!~'btrim[(].+[)][[:space:]]*<>' AND check_clause!~'[[:space:]]*<>[[:space:]]*['']{2}') OR
(check_clause~'~.+(\{[[:digit:]]+,\}|\{,[[:digit:]]+\})') 
ORDER BY domain_schema, domain_name;
Drop the constraint that is associated with the domain.

Collections where the query belongs to

Collection nameCollection description
Find problems automaticallyQueries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

Categories where the query belongs to

Category nameCategory description
Data at the database logical levelQueries of this category provide information about data in base tables.
Read user dataQueries of this category have to read data from user tables.
Result quality depends on namesQueries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results.
SecurityQueries of this category provide information about the security measures.

Reference materials for further reading

Reference
https://www.martinstoeckli.ch/hash/en/
https://crackstation.net/hashing-security.htm
https://www.nccgroup.trust/us/about-us/newsroom-and-events/blog/2015/march/enough-with-the-salts-updates-on-secure-password-schemes/

The list of all the queries