Find columns that potentially contains passwords and that participate in a unique constraint or index
Notes
The query considers primary key, unique, and exclude constraints as well as unique indexes (including indexes that are based on functions upper and lower).
Type
Problem detection (Each row in the result could represent a flaw in the design)
with keys as (select
o.conname,
(select nspname from pg_namespace where oid=c.relnamespace) as key_schema,
c.relname as key_table,
c.oid as key_table_oid,
o.conkey AS key_col,
case when o.contype='p' then 'PRIMARY KEY' when o.contype='x' then 'EXCLUDE' ELSE 'UNIQUE' END as contype
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype in ('u', 'p', 'x') ),
keys_unnest as (select conname, key_schema, key_table, key_table_oid, key_col, key_col_num, ordin, contype
from keys, unnest(keys.key_col) with ordinality as k(key_col_num, ordin)),
keys_with_names as (select conname, key_schema, key_table, contype, string_agg(a_key.attname, ', ' order by ordin) as key_col
from keys_unnest k inner join pg_attribute a_key on k.key_col_num = a_key.attnum and k.key_table_oid = a_key.attrelid and a_key.attisdropped = false
group by conname, key_schema, key_table, contype),
idxs as (select
c.relname as index_name,
n.nspname as index_schema,
c2.relname as index_table,
c2.oid as index_table_oid,
i.indkey AS ind_col,
'UNIQUE INDEX' AS contype
from pg_index i inner join pg_class c on i.indexrelid=c.oid
inner join pg_class c2 on i.indrelid=c2.oid
inner join pg_namespace n on c.relnamespace=n.oid
inner join pg_authid AS au on n.nspowner=au.oid
where (nspname='public' or rolname<>'postgres')
and indisunique=true
and not exists (select *
from pg_constraint
where pg_constraint.conindid=c.oid)),
idxs_unnest as (select index_name, index_schema, index_table, index_table_oid, ind_col, ind_col_num, ordin, contype
from idxs, unnest(idxs.ind_col) with ordinality as k(ind_col_num, ordin)),
idxs_with_names as (select index_name, index_schema, index_table, contype, string_agg(a_key.attname, ', ' order by ordin) as ind_col
from idxs_unnest k inner join pg_attribute a_key on k.ind_col_num = a_key.attnum and k.index_table_oid = a_key.attrelid and a_key.attisdropped = false
group by index_name, index_schema, index_table, contype),
cols as (select conname as object_name, key_schema as table_schema, key_table as table_name, contype as object_type, key_col as column_name
from keys_with_names
where key_col~*'(parool|salasona|password|pswd|pwd)'
and key_col!~*'(sool|salt)'
union select index_name, index_schema, index_table, contype, ind_col
from idxs_with_names
where ind_col~*'(parool|salasona|password|pswd|pwd)'
union SELECT indexname, schemaname, tablename, 'UNIQUE FUNCTION-BASED INDEX' AS index_type, substring(indexdef, '[(].*[)]') AS index_col
FROM pg_indexes
WHERE indexdef~*'unique[[:space:]]+index.+(upper|lower)[(].*(parool|salasona|passw|pswd|pwd|pw).*[)]'
and indexdef!~*'(sool|salt)'
AND schemaname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL))
select object_name, object_type, table_schema, table_name, column_name
from cols INNER JOIN INFORMATION_SCHEMA.columns USING (table_schema, table_name, column_name)
WHERE data_type!~*'(smallint|integer|bigint|numeric)'
order by table_schema, table_name, object_type;
SQL statements that help generate fixes for the identified problem.
SQL Query to Generate Fix
Description
with keys as (select
o.conname,
(select nspname from pg_namespace where oid=c.relnamespace) as key_schema,
c.relname as key_table,
c.oid as key_table_oid,
o.conkey AS key_col,
case when o.contype='p' then 'PRIMARY KEY' when o.contype='x' then 'EXCLUDE' ELSE 'UNIQUE' END as contype
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype in ('u', 'p', 'x') ),
keys_unnest as (select conname, key_schema, key_table, key_table_oid, key_col, key_col_num, ordin, contype
from keys, unnest(keys.key_col) with ordinality as k(key_col_num, ordin)),
keys_with_names as (select conname, key_schema, key_table, contype, string_agg(a_key.attname, ', ' order by ordin) as key_col
from keys_unnest k inner join pg_attribute a_key on k.key_col_num = a_key.attnum and k.key_table_oid = a_key.attrelid and a_key.attisdropped = false
group by conname, key_schema, key_table, contype)
SELECT format('ALTER TABLE %1$I.%2$I DROP CONSTRAINT %3$I;', key_schema, key_table, conname) AS statements
from keys_with_names
where key_col~*'(parool|salasona|password|pswd|pwd)'
order by key_schema, key_table, contype;
Drop the primary key, unique, or exclude constraint.
with idxs as (select
c.relname as index_name,
n.nspname as index_schema,
c2.relname as index_table,
c2.oid as index_table_oid,
i.indkey AS ind_col,
'UNIQUE INDEX' AS contype
from pg_index i inner join pg_class c on i.indexrelid=c.oid
inner join pg_class c2 on i.indrelid=c2.oid
inner join pg_namespace n on c.relnamespace=n.oid
inner join pg_authid AS au on n.nspowner=au.oid
where (nspname='public' or rolname<>'postgres')
and indisunique=true
and not exists (select *
from pg_constraint
where pg_constraint.conindid=c.oid)),
idxs_unnest as (select index_name, index_schema, index_table, index_table_oid, ind_col, ind_col_num, ordin, contype
from idxs, unnest(idxs.ind_col) with ordinality as k(ind_col_num, ordin)),
idxs_with_names as (select index_name, index_schema, index_table, contype, string_agg(a_key.attname, ', ' order by ordin) as ind_col
from idxs_unnest k inner join pg_attribute a_key on k.ind_col_num = a_key.attnum and k.index_table_oid = a_key.attrelid and a_key.attisdropped = false
group by index_name, index_schema, index_table, contype)
SELECT format('DROP INDEX %1$I.%2$I;', index_schema, index_name) AS statements
from idxs_with_names
where ind_col~*'(parool|salasona|password|pswd|pwd)'
order by index_schema, index_name;
Drop the unique non function-based index.
SELECT format('DROP INDEX %1$I.%2$I;', schemaname, indexname) AS statements
FROM pg_indexes
WHERE indexdef~*'unique[[:space:]]+index.+(upper|lower)[(].*(parool|salasona|password|pswd|pwd).*[)]'
AND
schemaname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
ORDER BY schemaname, indexname;
Drop the unique function-based index.
Collections
This query belongs to the following collections:
Name
Description
Find problems automatically
Queries, 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
This query is classified under the following categories:
Name
Description
Security
Queries of this category provide information about the security measures.
Uniqueness
Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.
Validity and completeness
Queries of this category provide information about whether database design represents the world (domain) correctly (validity) and whether database design captures all the information about the world (domain) that is correct and relevant (completeness).