postgresql

Babelfish for PostgreSQL

by

This has the capacity to be huge:

Babelfish for PostgreSQL is an Apache-2.0 open source project that adds a Microsoft SQL Server-compatible end-point to PostgreSQL to enable your PostgreSQL database to understand the SQL Server wire protocol and commonly used SQL Server commands. With Babelfish, applications that were originally built for SQL Server can work directly with PostgreSQL, with little to no code changes, and without changing database drivers.

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)

Postgresql Unused Indexes

by

To find indexes that have not been used since the last statistics reset with pg_stat_reset(), run this on your production server:

SELECT 
    u.schemaname,
    u.relname AS tablename,
    u.indexrelname AS indexname,
    pg_relation_size(u.indexrelid) AS index_size
FROM 
    pg_catalog.pg_stat_user_indexes u
    JOIN pg_catalog.pg_index i ON u.indexrelid = i.indexrelid
WHERE
    u.idx_scan = 0           -- never been scanned
    AND 0 <> ALL (i.indkey)  -- no index column is an expression
    AND NOT EXISTS           -- index not used to enforce a constraint
       (SELECT 1 FROM pg_catalog.pg_constraint c WHERE c.conindid = u.indexrelid)
ORDER BY 
    pg_relation_size(u.indexrelid) DESC;

Indexes that are very rarely used can also be good candidates for removal; replace u.idx_scan = 0 in the query with a different threshold, e.g. u.idx_scan < 5. BUT be aware of indexes whose purpose is to support queries which are run infrequently such as monthly reporting queries.

And, always script out your indexes before you remove them.

Further useful index scripts can be found here.

Postgres Configuration

by

Configuration file locations:

Where are my postgres *.conf files?

Where is the Postgresql config file: ‘postgresql.conf’ on Windows?

  • Windows: C:\Program Files\PostgreSQL\x.x\data\postgresql.conf
  • Linux: /etc/postgresql/x.x/main/postgresql.conf

Go to bottom of .conf file, and add this line:

include postgresql.custom.conf

Then create file ‘postgresql.custom.conf’ in the same directory and place your customised configuration settings in it. Any settings set in the custom file will override those in the main config.

Navigate to pgtune and enter the required information, and pgtune will generate custom settings based upon total RAM size and intended use etc:

image

Copy the generated settings into file ‘postgresql.custom.conf’:

max_connections = 100
shared_buffers = 8GB
effective_cache_size = 24GB
work_mem = 83886kB
maintenance_work_mem = 2GB
min_wal_size = 2GB
max_wal_size = 4GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1

Restart Postgres.

Further reading on Postgres performance: http://www.craigkerstiens.com