CREATE OR REPLACE FUNCTION f_check_password() RETURNS TEXT AS $$
DECLARE
sql_stmt TEXT;
val TEXT ;
passwd_columns RECORD;
table_schema TEXT;
table_name TEXT;
column_name TEXT;
sample_value TEXT;
return_str TEXT:='';
BEGIN
RAISE NOTICE 'Checking whether password is open text';
FOR passwd_columns IN SELECT c.table_schema, c.table_name, c.column_name
FROM INFORMATION_SCHEMA.columns AS c INNER JOIN INFORMATION_SCHEMA.tables AS t USING
(table_schema, table_name) WHERE c.data_type IN ('character', 'character varying', 'text') AND
t.table_type='BASE TABLE' AND c.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) AND
c.column_name~*'(parool|salasona|passw*|pwd)'
ORDER BY c.table_schema, c.table_name LOOP
table_schema:= passwd_columns.table_schema;
table_name:= passwd_columns.table_name;
column_name:= passwd_columns.column_name;
sql_stmt:='SELECT ' || quote_ident(column_name) || ' AS c FROM ' || quote_ident(table_schema) ||'.' || quote_ident(table_name) || ' LIMIT 1';
EXECUTE sql_stmt INTO sample_value;
return_str:=return_str || ' ' || quote_ident(table_schema) || '.' || quote_ident(table_name) || '.' || quote_ident(column_name) || '=' || sample_value;
END LOOP;
RAISE NOTICE 'Detection completed';
RETURN return_str;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER
SET search_path = information_schema, pg_temp;
SELECT f_check_password() AS sample_values;
DROP FUNCTION IF EXISTS f_check_password();