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;