password

Postgresql: Find Users With Weak Passwords

by

A while back I wrote a short post that checks for SQL Server SQL logins with weak passwords. Here’s the equivalent for Postgresql (it’s only checking the MD5 hash algorithm at present):

CREATE TEMPORARY TABLE temp_CommonPasswords
(
    Password varchar(30) not null primary key
)
ON COMMIT DROP;
 
INSERT INTO temp_CommonPasswords(Password) VALUES
(''),
('123'),
('1234'),
('12345'),
('123456'),
('1234567'),
('12345678'),
('123456789'),
('1234567890'),
('987654321'),
('123qwe'),
('mynoob'),
('18atcskd2w'),
('55555'),
('555555'),
('3rjs1la7qe'),
('google'),
('zxcvbnm'),
('000000'),
('1q2w3e'),
('1q2w3e4r5t'),
('1q2w3e4r'),
('qwerty'),
('qwerty123'),
('password'),
('p@ssword'),
('p@ssw0rd'),
('password1'),
('p@ssword1'),
('password123'),
('passw0rd'),
('111111'),
('1111111'),
('abc123'),
('666666'),
('7777777'),
('654321'),
('123123'),
('123321'),
('iloveyou'),
('admin'),
('nimda'),
('welcome'),
('welcome!'),
('!@#$%^&*'),
('aa123456'),
('lovely'),
('sunshine'),
('shadow'),
('princess' ),
('solo'),
('football'),
('monkey'),
('Monkey'),
('charlie'),
('donald'),
('Donald'),
('dragon'),
('Dragon'),
('trustno1'),
('letmein'),
('whatever'),
('hello'),
('freedom'),
('master'),
('starwars'),
('qwertyuiop'),
('Qwertyuiop'),
('qazwsx'),
('corona'),
('woke'),
('batman'),
('superman'),
('login');
 
 
SELECT
    usename
FROM
    pg_shadow
    cross join lateral (Select Password from temp_CommonPasswords) c
WHERE
    'md5'||md5(c.Password||usename) = pg_shadow.passwd
 
UNION ALL
 
SELECT
    usename
FROM
    pg_shadow
WHERE
    passwd = 'md5'||md5(usename||usename)