chkpass

chkpass : data type for auto-encrypted passwords

Overview

Packages

Source

pig build pkg chkpass;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install chkpass;		# install via package name, for the active PG version

pig install chkpass -v 18;   # install for PG 18
pig install chkpass -v 17;   # install for PG 17
pig install chkpass -v 16;   # install for PG 16
pig install chkpass -v 15;   # install for PG 15
pig install chkpass -v 14;   # install for PG 14

Create this extension with:

CREATE EXTENSION chkpass;

Usage

chkpass: auto-encrypted password data type

The chkpass extension provides a data type for storing encrypted passwords. Originally bundled with PostgreSQL (removed in PG 11), this is a standalone version for modern PostgreSQL.

CREATE EXTENSION chkpass;

Data Type

The chkpass type automatically encrypts passwords using Unix crypt() on input and stores only the encrypted form.

CREATE TABLE accounts (
    username text PRIMARY KEY,
    password chkpass
);

INSERT INTO accounts VALUES ('admin', 'mysecretpassword');

Operators

The = operator checks a plaintext password against the stored encrypted value:

SELECT * FROM accounts WHERE password = 'mysecretpassword';
-- Returns the matching row if the password is correct

Behavior

• Passwords are automatically encrypted on input – the plaintext is never stored
• Output displays the encrypted hash, not the original password
• Comparison with = encrypts the right-hand operand and compares hashes
• Uses the standard Unix crypt() function for encryption