Security functions

Hash and check passwords, make SHA-256 hashes, random tokens and random ids (UUIDs), and encrypt data with PostgreSQL's pgcrypto functions.

• Category: Functions — security
• Keywords: security, password, hash, salt, pbkdf2, login, register, sign up, session, session token, api key, reset link, uuid, random, crypto, pgcrypto, SHA2, HMAC, webhook signature, digest, sha224, sha384, sha512, sha256 bytea, encode sha256 hex, encrypt, decrypt, encryption, pgp, openpgp, gpg, pgp_sym_encrypt, pgp_sym_decrypt, crypt, gen_salt, bcrypt, gen_random_bytes, armor, dearmor, aes, base64
• See also: Users and roles, Keeping kdb serve safe, Bytes, hex and base64, Constraints, UPDATE and DELETE

These functions help your app keep its users safe: people who register and log in to your web site or mobile app.

• A hash is a short code made from a text. You can not get the text back from the hash.
• password_hash and password_verify are for passwords. They are slow on purpose (about 0.05 to 0.4 seconds), so a thief who steals the hashes can only try a few guesses per second.
• sha256 and hmac_sha256 are fast. Use them for tokens and signatures, never for passwords.
• random_bytes, random_token and gen_random_uuid take random bytes from the operating system. They give a new value on every call and for every row.
• A NULL argument gives NULL.

Database users (who may connect to the KDB server itself) are a different thing: see Users and roles.

Example: Register a user, then check the password at login

Setup:

CREATE TABLE app_users (id INT PRIMARY KEY AUTO, email STRING NOT NULL UNIQUE, password_hash STRING NOT NULL);

INSERT INTO app_users (email, password_hash) VALUES ('sita@example.com', password_hash('correct horse battery'));
SELECT id, email, password_hash FROM app_users;
SELECT id FROM app_users
WHERE email = 'sita@example.com' AND password_verify('correct horse battery', password_hash);

Result:

INSERT INTO app_users (email, password_hash) VALUES ('sita@example.com', '$pbkdf2-sha256$i=600000$vl

1 row(s) inserted

SELECT id, email, password_hash FROM app_users

(1 row)

SELECT id FROM app_users WHERE email = 'sita@example.com' AND password_verify('correct horse battery

(1 row)

password_hash

Syntax:

password_hash(password)
password_hash(password, iterations)

Makes a hash of a password, to store in your users table. Each call uses a new random salt (random bytes mixed into the hash), so the same password gives a different hash every time.

• Returns: STRING like $pbkdf2-sha256$i=600000$<salt>$<hash>. It says how it was made (PBKDF2 with HMAC-SHA-256), so password_verify can check it later, even after the default changes.
• NULL: NULL in, NULL out.
• Notes:
  • With 600000 rounds one call takes about 0.05 to 0.1 seconds on a PC with SHA instructions in the CPU, about 0.4 seconds without them.
  • In INSERT ... VALUES and UPDATE ... SET, a password_hash('...') with plain text is made before the statement takes the database lock, so other statements do not wait. The statement KDB shows back has the finished hash, not the password.
  • Because of that, UPDATE app_users SET password_hash = password_hash('x') gives every changed row the same hash (one salt). Change passwords one row at a time (WHERE id = ...).
  • An empty password '' is accepted. Check the length in your app.
  • Do not compare two hashes with =: each hash has its own salt, so they never match. Use password_verify.
  • PostgreSQL's pgcrypto way, crypt(pw, gen_salt('bf')), works in KDB too: see crypt. Use it when the hashes must also work in PostgreSQL. MySQL has no safe built-in password hash. KDB's text follows the common $name$settings$salt$hash style, but other tools may write the salt and hash a little differently.

Example: The same password twice gives two different hashes

SELECT password_hash('correct horse battery') AS first, password_hash('correct horse battery') AS second;

Result:

(1 row)

Example: Register: store the hash, never the password

Setup:

CREATE TABLE app_users (id INT PRIMARY KEY AUTO, email STRING NOT NULL UNIQUE, password_hash STRING NOT NULL, created_at TIMESTAMP DEFAULT now());

INSERT INTO app_users (email, password_hash) VALUES ('sita@example.com', password_hash('correct horse battery'));
SELECT id, email, password_hash FROM app_users;

Result:

INSERT INTO app_users (email, password_hash) VALUES ('sita@example.com', '$pbkdf2-sha256$i=600000$nf

1 row(s) inserted

SELECT id, email, password_hash FROM app_users

(1 row)

Example: The email is already registered (shows an error)

Setup:

CREATE TABLE app_users (id INT PRIMARY KEY AUTO, email STRING NOT NULL UNIQUE, password_hash STRING NOT NULL);
INSERT INTO app_users (email, password_hash) VALUES ('sita@example.com', password_hash('correct horse battery'));

INSERT INTO app_users (email, password_hash) VALUES ('sita@example.com', password_hash('another password'));

Result:

Error: column 'email' must be unique: 'sita@example.com' is already used

Example: Surprise: one UPDATE for many rows gives them all the same hash

Setup:

CREATE TABLE app_users (id INT PRIMARY KEY AUTO, email STRING NOT NULL UNIQUE, password_hash STRING NOT NULL);
INSERT INTO app_users (email, password_hash) VALUES ('sita@example.com', 'x'), ('ram@example.com', 'y');

UPDATE app_users SET password_hash = password_hash('Temporary-pass-1');
SELECT id, password_hash FROM app_users;

Result:

UPDATE app_users SET password_hash = '$pbkdf2-sha256$i=600000$x0cnx3AxK+atLxX4HGHTwA$KHrHm3PXCPdEb8D

2 row(s) updated

SELECT id, password_hash FROM app_users

(2 rows)

Example: Fewer rounds: faster, but easier to guess (shows an error)

SELECT password_hash('my password', 1000) AS ok_but_weak;
SELECT password_hash('my password', 500) AS too_few;

Result:

(1 row)

Error: line 2, column 8: password_hash: iterations must be a whole number from 1000 to 10000000 (default 600000)

password_verify

Syntax:

password_verify(password, hash)

Checks if a typed password matches a hash made by password_hash.

• Returns: BOOL: true when the password is right, false when it is wrong or when the hash is damaged or not a KDB hash. It never gives an error.
• NULL: NULL when an argument is NULL.
• Notes:
  • It is as slow as password_hash. Call it once per login.
  • In WHERE, put the email test first: WHERE email = ... AND password_verify(...). Then only the one matching row is checked. The other order checks the password of every user.
  • Show the same message for "no such email" and "wrong password" (for example "wrong email or password"), so nobody can find out which emails are registered.
  • It reads the rounds from the hash, so old hashes with other rounds still work.

Example: Log in: right password gives the id, wrong password gives no row

Setup:

CREATE TABLE app_users (id INT PRIMARY KEY AUTO, email STRING NOT NULL UNIQUE, password_hash STRING NOT NULL);
INSERT INTO app_users (email, password_hash) VALUES ('sita@example.com', password_hash('correct horse battery'));

SELECT id FROM app_users WHERE email = 'sita@example.com' AND password_verify('correct horse battery', password_hash);
SELECT id FROM app_users WHERE email = 'sita@example.com' AND password_verify('wrong guess', password_hash);

Result:

SELECT id FROM app_users WHERE email = 'sita@example.com' AND password_verify('correct horse battery

(1 row)

SELECT id FROM app_users WHERE email = 'sita@example.com' AND password_verify('wrong guess', passwor

(0 rows)

Example: Wrong, damaged and NULL hashes

SELECT password_verify('secret-123', password_hash('secret-123', 1000)) AS right_password,
       password_verify('Secret-123', password_hash('secret-123', 1000)) AS capital_s,
       password_verify('secret-123', 'not a hash') AS damaged,
       password_verify('secret-123', NULL) AS no_hash;

Result:

(1 row)

Example: Change a password: check the old one, then store a new hash

Setup:

CREATE TABLE app_users (id INT PRIMARY KEY AUTO, email STRING NOT NULL UNIQUE, password_hash STRING NOT NULL);
INSERT INTO app_users (email, password_hash) VALUES ('sita@example.com', password_hash('old password 1'));

SELECT password_verify('old password 1', password_hash) AS old_is_right FROM app_users WHERE id = 1;
UPDATE app_users SET password_hash = password_hash('new password 2') WHERE id = 1;
SELECT password_verify('old password 1', password_hash) AS old_works, password_verify('new password 2', password_hash) AS new_works
FROM app_users WHERE id = 1;

Result:

SELECT password_verify('old password 1', password_hash) AS old_is_right FROM app_users WHERE id = 1

(1 row)

UPDATE app_users SET password_hash = '$pbkdf2-sha256$i=600000$feo0AWDSoGFGHM+3dv+ruw$8aZsUz7MpEqozA+

1 row(s) updated

SELECT password_verify('old password 1', password_hash) AS old_works, password_verify('new password

(1 row)

sha256

Syntax:

sha256(text)
sha256(bytes)
sha224(...), sha384(...), sha512(...)

The SHA-256 hash of a value. The same value always gives the same hash. It is fast. sha224, sha384 and sha512 are the other SHA-2 sizes and work the same way.

• Returns:
  • text in: STRING of lower-case hex characters (64 for sha256), KDB's own handy form.
  • BYTES in: BYTES, the raw hash (32 bytes for sha256), as PostgreSQL's sha256(bytea) gives.
• NULL: NULL in, NULL out.
• Notes:
  • The text is hashed as UTF-8 bytes, so text in any language (Nepali, Chinese, emoji) works.
  • Never use it for passwords: it is far too fast to guess. Use password_hash.
  • Good uses: store the hash of a session token or reset token (a stolen copy of the table can not be used to log in), find the same news article loaded twice, make a short key from a long text.
  • PostgreSQL code works as it is: encode(sha256(convert_to(token, 'UTF8')), 'hex') and encode(sha256('abc'), 'hex') give the 64 hex characters of the hash (KDB encodes the hash's own bytes, not its hex text again).
  • For other hash kinds by name, use digest. MySQL's SHA2(text, 256) returns hex like KDB's text form.

Example

SELECT sha256('abc') AS abc, sha256('') AS empty, sha256(NULL) AS null_value;

Result:

(1 row)

Example: Bytes in, bytes out; the PostgreSQL way to get hex

SELECT sha256('abc'::bytea) AS raw_hash,
       encode(sha256(convert_to('abc', 'UTF8')), 'hex') AS pg_style_hex,
       encode(sha256('abc'), 'hex') = sha256('abc') AS same_hex,
       sha512('abc') AS sha512_hex;

Result:

(1 row)

Example: Nepali text, and a number is hashed as its text

SELECT sha256('नेपाल') AS nepal, sha256(123) = sha256('123') AS number_as_text, length(sha256('any text at all')) AS hex_chars;

Result:

(1 row)

Example: Find news articles that were loaded twice

Setup:

CREATE TABLE news (id INT PRIMARY KEY AUTO, title STRING, body STRING);
INSERT INTO news (title, body) VALUES
  ('Index up 20 points', 'The index closed higher today.'),
  ('Index gains', 'The index closed higher today.'),
  ('Bank results', 'CITY reported its quarterly profit.');

SELECT sha256(body) AS body_hash, count(*) AS copies
FROM news
GROUP BY sha256(body)
HAVING count(*) > 1;

Result:

(1 row)

digest

Syntax:

digest(value, type)

The hash of value as bytes, with the kind named by type (PostgreSQL's pgcrypto).

• Returns: BYTES. Use encode(..., 'hex') for hex text.
• NULL: NULL in, NULL out.
• Notes: another type is an error. md5 and sha1 are broken for security; use them only to match what another system gives.

Example: The same text, three hash kinds

SELECT encode(digest('abc', 'sha1'), 'hex') AS sha1,
       encode(digest('abc', 'md5'), 'hex') AS md5,
       encode(digest('abc', 'sha256'), 'hex') = sha256('abc') AS same_as_sha256;

Result:

(1 row)

hmac_sha256

Syntax:

hmac_sha256(key, message)

A signature of a message made with a secret key (HMAC with SHA-256). Only someone who knows the key can make the same signature. Payment gateways and webhooks (a web site that calls your app) use it to prove that a message is real and was not changed.

• Returns: STRING of 64 lower-case hex characters.
• NULL: NULL when an argument is NULL.
• Notes: the order is key first, then the message. PostgreSQL's pgcrypto writes hmac(message, key, 'sha256') (message first, returns BYTEA); MySQL has no HMAC function.

Example

SELECT hmac_sha256('key', 'The quick brown fox jumps over the lazy dog') AS signature;

Result:

(1 row)

Example: Check a webhook: is the signature in the request real?

SELECT hmac_sha256('whsec_shop_secret_42', '{"order":1001,"amount":1500}')
         = 'f30505d17def40c02ad9b90fdaef65762d0c6b69cc93843c3228b506340bcfc1' AS real_message,
       hmac_sha256('whsec_shop_secret_42', '{"order":1001,"amount":15}')
         = 'f30505d17def40c02ad9b90fdaef65762d0c6b69cc93843c3228b506340bcfc1' AS amount_changed;

Result:

(1 row)

The signature f30505d1... came with the request. Only the real message with the right key gives the same signature. Compare signatures in your app with a constant-time compare when you can.

random_bytes

Syntax:

random_bytes(n)

n random bytes from the operating system, written as hex (2 characters per byte).

• Returns: STRING of 2 * n hex characters.
• NULL: random_bytes(NULL) is NULL.
• Notes: a new value for every call and every row. For tokens in links and cookies, random_token is shorter (URL-safe text). For raw BYTES, as PostgreSQL gives them, use gen_random_bytes.

Example

SELECT random_bytes(4) AS four_bytes, random_bytes(16) AS api_secret, length(random_bytes(16)) AS hex_chars;

Result:

(1 row)

Example: The number must be from 1 to 1024 (shows an error)

SELECT random_bytes(0);

Result:

Error: line 1, column 8: random_bytes: the number of bytes must be a whole number from 1 to 1024

Example: The number is required (shows an error)

SELECT random_bytes();

Result:

Error: line 1, column 8: function 'random_bytes' takes 1 argument(s), got 0

random_token

Syntax:

random_token()
random_token(n)

n random bytes (default 32) as URL-safe base64 text: letters, digits, - and _. It can go into a link or a cookie as it is. 32 bytes give 43 characters.

• Returns: STRING.
• NULL: random_token(NULL) is NULL.
• Notes:
  • Use it for session tokens, "forgot password" links, email confirm links and API keys.
  • Send the token to the user, but store only sha256(token). Then a stolen copy of the table can not be used.
  • A new value for every call and every row.

Example

SELECT random_token() AS session_token, random_token(16) AS shorter, length(random_token()) AS chars;

Result:

(1 row)

Example: Forgot password: store the hash of a reset token for 30 minutes

Setup:

CREATE TABLE password_resets (token_hash STRING PRIMARY KEY, user_id INT NOT NULL, expires_at TIMESTAMP NOT NULL);

SELECT random_token() AS token_for_the_email_link;
INSERT INTO password_resets VALUES (sha256('Qm9vSw1mY3Jk0x7Lr2nT5uV8wZ1aB4cD6eF9gH2jK3m'), 1, dateadd('m', 30, now()));
SELECT user_id FROM password_resets
WHERE token_hash = sha256('Qm9vSw1mY3Jk0x7Lr2nT5uV8wZ1aB4cD6eF9gH2jK3m') AND expires_at > now();

Result:

SELECT random_token() AS token_for_the_email_link

(1 row)

INSERT INTO password_resets VALUES (sha256('Qm9vSw1mY3Jk0x7Lr2nT5uV8wZ1aB4cD6eF9gH2jK3m'), 1, datead

1 row(s) inserted

SELECT user_id FROM password_resets WHERE token_hash = sha256('Qm9vSw1mY3Jk0x7Lr2nT5uV8wZ1aB4cD6eF9g

(1 row)

Example: Too many bytes (shows an error)

SELECT random_token(2000);

Result:

Error: line 1, column 8: random_token: the number of bytes must be a whole number from 1 to 1024

gen_random_uuid

Syntax:

gen_random_uuid()

A random id in the standard UUID form (version 4), like 0465d47f-8194-4c94-a372-eda3b70504a0. Two calls practically never give the same id.

• Returns: STRING of 36 characters. KDB has no separate UUID column type: use a STRING column.
• NULL: takes no arguments.
• Notes:
  • Good as a public id of a forum post or an order, when you do not want people to guess the next id (/post/7, /post/8, ...).
  • DEFAULT gen_random_uuid() gives each new row its own id.
  • ALTER TABLE ... ADD COLUMN id STRING DEFAULT gen_random_uuid() gives all old rows one and the same value; only new rows get their own.
  • The same name and form as PostgreSQL 13 and newer. MySQL's UUID() makes version 1 ids (based on time, not random).

Example

SELECT gen_random_uuid() AS id, length(gen_random_uuid()) AS chars;

Result:

(1 row)

Example: Forum posts with a random public id

Setup:

CREATE TABLE forum_posts (public_id STRING PRIMARY KEY DEFAULT gen_random_uuid(), author STRING, title STRING);

INSERT INTO forum_posts (author, title) VALUES ('sita', 'CITY dividend news'), ('ram', 'बजार आज कस्तो होला?');
SELECT public_id, author, title FROM forum_posts;

Result:

INSERT INTO forum_posts (author, title) VALUES ('sita', 'CITY dividend news'), ('ram', 'बजार आज कस्त

2 row(s) inserted

SELECT public_id, author, title FROM forum_posts

(2 rows)

Example: No arguments (shows an error)

SELECT gen_random_uuid(4);

Result:

Error: line 1, column 8: function 'gen_random_uuid' takes 0 argument(s), got 1

PostgreSQL pgcrypto functions

KDB has the functions of PostgreSQL's pgcrypto extension, and they give the same bytes. Data encrypted in PostgreSQL opens in KDB, and data encrypted in KDB opens in PostgreSQL (and in GnuPG). There is no CREATE EXTENSION to run: they are always there.

Errors use PostgreSQL's words and codes. Most are SQLSTATE 39000, for example Wrong key or corrupt data.

pgp_sym_encrypt

Syntax:

pgp_sym_encrypt(text, password)
pgp_sym_encrypt(text, password, options)
pgp_sym_encrypt_bytea(bytes, password [, options])

Encrypts with a password. The result is an OpenPGP message (RFC 4880), the format of GnuPG: a key made from the password with a random salt (65536 to 253952 bytes of SHA-1 hashing), AES-128, and a SHA-1 check that finds any change to the message.

• Returns: BYTES. The same text gives different bytes every time (a new salt and a random start).
• NULL: NULL in, NULL out.
• Notes:
  • Keep the result in a BYTES column. armor() turns it into text that GnuPG reads: gpg --decrypt.
  • Each call hashes the password again (on purpose, it makes guessing slow): about 0.05 ms per row on a CPU with SHA instructions. For millions of rows, s2k-count=1024 is about 10 times faster but easier to guess.
  • cast5, twofish and compress-algo 1 or 2 are refused (SQLSTATE 0A000); PostgreSQL has them. pgp_pub_encrypt (public keys) is refused too.

Example: Encrypt a secret, read it back

SELECT pgp_sym_decrypt(pgp_sym_encrypt('secret', 'pw'), 'pw') AS back,
       length(pgp_sym_encrypt('secret', 'pw')) AS bytes;

Result:

(1 row)

Example: Store encrypted phone numbers

Setup:

CREATE TABLE customers (id INT PRIMARY KEY AUTO, name STRING, phone BYTES);

INSERT INTO customers (name, phone) VALUES ('Sita', pgp_sym_encrypt('9841000001', 'shop-key-2026', 'cipher-algo=aes256'));
SELECT name, pgp_sym_decrypt(phone, 'shop-key-2026') AS phone FROM customers;

Result:

INSERT INTO customers (name, phone) VALUES ('Sita', pgp_sym_encrypt('9841000001', 'shop-key-2026', '

1 row(s) inserted

SELECT name, pgp_sym_decrypt(phone, 'shop-key-2026') AS phone FROM customers

(1 row)

Example: As text GnuPG reads

SELECT armor(pgp_sym_encrypt('hello gpg', 'pw')) AS message;

Result:

(1 row)

pgp_sym_decrypt

Syntax:

pgp_sym_decrypt(message, password [, options])
pgp_sym_decrypt_bytea(message, password [, options])

Opens a message made by pgp_sym_encrypt, by PostgreSQL's pgcrypto or by gpg --symmetric.

• Returns: pgp_sym_decrypt gives STRING, pgp_sym_decrypt_bytea gives BYTES.
• NULL: NULL in, NULL out.
• Notes:
  • A wrong password, a changed byte or a cut message all give one error: Wrong key or corrupt data (SQLSTATE 39000). It never gives wrong text.
  • It reads AES-128/192/256, Blowfish and 3DES, all key kinds, and ZIP and ZLIB compression (what GnuPG writes by default).
  • Data encrypted as bytes gives Not text data in pgp_sym_decrypt: use pgp_sym_decrypt_bytea. Text that is not valid UTF-8 gives SQLSTATE 22021.

Example: The wrong password is an error, never wrong text (shows an error)

SELECT pgp_sym_decrypt(pgp_sym_encrypt('secret', 'pw'), 'pw') AS right_password;
SELECT pgp_sym_decrypt(pgp_sym_encrypt('secret', 'pw'), 'guess') AS wrong_password;

Result:

(1 row)

Error: line 2, column 8: Wrong key or corrupt data

Example: A message PostgreSQL's pgcrypto made (password foobar)

SELECT pgp_sym_decrypt(dearmor('-----BEGIN PGP MESSAGE-----

jA0EBwMCci97v0Q6Z0Zg0kQBsVf5Oe3iC+FBzUmuMV9KxmAyOMyjCc/5i8f1Eest
UTAsG35A1vYs02VARKzGz6xI2UHwFUirP+brPBg3Ee7muOx8pA==
=XtrP
-----END PGP MESSAGE-----'), 'foobar') AS from_postgres;

Result:

(1 row)

encrypt

Syntax:

encrypt(data, key, type)
decrypt(data, key, type)
encrypt_iv(data, key, iv, type)
decrypt_iv(data, key, iv, type)

Raw encryption with a key you keep yourself. type is the cipher, then optionally -mode and /pad::

• Returns: BYTES. Use convert_from(decrypt(...), 'UTF8') for text.
• NULL: NULL in, NULL out.
• Notes:
  • A short key is filled with zero bytes, as pgcrypto does: 'k' is the AES-128 key 6b000000....
  • The same data and key always give the same bytes (the IV is zero). encrypt_iv takes your own IV.
  • decrypt can not tell a wrong key: it gives wrong bytes, as in PostgreSQL. Prefer pgp_sym_encrypt, which checks.
  • A wrong type gives Cannot use "...": No such cipher algorithm (SQLSTATE 22023).
  • A statement that uses one key for many rows prepares the key once.

Example: Encrypt and decrypt with AES

SELECT encrypt('x', 'k', 'aes') AS encrypted,
       convert_from(decrypt(encrypt('x', 'k', 'aes'), 'k', 'aes'), 'UTF8') AS back;

Result:

(1 row)

Example: The FIPS 197 test: one AES block, no padding

SELECT encode(encrypt('\x00112233445566778899aabbccddeeff'::bytea, '\x000102030405060708090a0b0c0d0e0f'::bytea, 'aes-ecb/pad:none'), 'hex') AS block;

Result:

(1 row)

Example: Not a cipher (shows an error)

SELECT encrypt('x', 'k', 'rot13');

Result:

Error: line 1, column 8: Cannot use "rot13": No such cipher algorithm

crypt

Syntax:

crypt(password, salt)
gen_salt(type)
gen_salt(type, rounds)

pgcrypto's password hashes. gen_salt makes a new random salt; crypt makes the hash. To check a password later, give crypt the stored hash as the salt: the right password gives the same hash back.

• Returns: STRING.
• NULL: NULL in, NULL out.
• Notes:
  • The hashes are the same as PostgreSQL's (and Linux's crypt()), so users can move between the two.
  • bcrypt uses only the first 72 bytes of a password.
  • A salt it can not use gives crypt(3) returned NULL (SQLSTATE 39000). An unknown type in gen_salt gives SQLSTATE 22023.
  • A high rounds can take very long (31 is hours); the statement timeout stops it.
  • KDB's own password_hash is the simpler choice when PostgreSQL does not need the hashes.

Example: Register and log in with bcrypt

Setup:

CREATE TABLE app_users (email STRING PRIMARY KEY, pw_hash STRING NOT NULL);

INSERT INTO app_users VALUES ('sita@example.com', crypt('correct horse', gen_salt('bf', 8)));
SELECT left(pw_hash, 7) AS kind,
       crypt('correct horse', pw_hash) = pw_hash AS right_password,
       crypt('wrong guess', pw_hash) = pw_hash AS wrong_password
FROM app_users;

Result:

INSERT INTO app_users VALUES ('sita@example.com', crypt('correct horse', gen_salt('bf', 8)))

1 row(s) inserted

SELECT left(pw_hash, 7) AS kind, crypt('correct horse', pw_hash) = pw_hash AS right_password, crypt(

(1 row)

Example: The same hash as PostgreSQL and Linux

SELECT crypt('pw', '$1$saltsalt') AS md5_crypt, crypt('pw', 'ab') AS old_des;

Result:

(1 row)

Example: Too few rounds (shows an error)

SELECT gen_salt('bf', 3);

Result:

Error: line 1, column 8: gen_salt: Incorrect number of rounds

gen_random_bytes

Syntax:

gen_random_bytes(n)

n random bytes from the operating system's secure random generator, as BYTES (pgcrypto's form; random_bytes gives hex text).

• Returns: BYTES.
• NULL: NULL in, NULL out.
• Notes: a new value for every call and every row. Another n gives Length not in range (SQLSTATE 39000).

Example

SELECT length(gen_random_bytes(16)) AS bytes, encode(gen_random_bytes(8), 'hex') AS hex;

Result:

(1 row)

armor

Syntax:

armor(data)
armor(data, keys, values)
dearmor(text)

armor writes bytes as OpenPGP text: -----BEGIN PGP MESSAGE-----, base64 lines of 76 characters and a CRC-24 check line. dearmor reads such text back into bytes. keys and values are two arrays of header lines (Comment: ...).

• Returns: armor gives STRING, dearmor gives BYTES.
• NULL: NULL in, NULL out.
• Notes: text with a wrong check line or a character that is not base64 gives Corrupt ascii-armor (SQLSTATE 39000). Text before and after the block is skipped.

Example: Bytes to text and back

SELECT armor('\x01'::bytea) AS text, dearmor(armor('\x01'::bytea)) AS back;

Result:

(1 row)

Example: With a header line

SELECT armor('\x01'::bytea, ARRAY['Comment'], ARRAY['made by KDB']) AS text;

Result:

(1 row)

base64

Syntax:

base64(data, maxLength)

QuestDB's function: the standard base64 text of the first maxLength bytes of data.

• Returns: STRING.
• NULL: NULL in, NULL out.
• Notes: maxLength must be more than 0. For whole values, encode(data, 'base64') and to_base64(data) work too.

Example

SELECT base64('ab', 10) AS whole, base64('abcdef', 3) AS first_three;

Result:

(1 row)

App register, login and sessions

Syntax:

register:  INSERT ... password_hash('password')
login:     SELECT ... WHERE email = ... AND password_verify('password', password_hash)
session:   random_token()  ->  store sha256(token) with an expiry time
request:   SELECT ... WHERE token_hash = sha256('token') AND expires_at > now()
log out:   DELETE ... WHERE token_hash = sha256('token')

The whole way an app lets people register, log in and stay logged in. Your backend (the server program of your app) runs these statements. The browser only ever gets the token.

• Returns: see each function above.
• NULL: a NULL token or password finds no row.
• Notes:
  • Always send user text as parameters of your driver ($1, $2), never glued into the SQL text (that allows SQL injection).
  • Count failed logins per email and per IP address in your app and wait longer after each failure. KDB does this for its own logins, not for your tables.
  • Delete old sessions now and then: DELETE FROM app_sessions WHERE expires_at < now();
  • After a password change, delete the user's other sessions.

Example: The complete flow: register, log in, session, request, log out

Setup:

CREATE TABLE app_users (
  id INT PRIMARY KEY AUTO,
  email STRING NOT NULL UNIQUE,
  password_hash STRING NOT NULL,
  created_at TIMESTAMP DEFAULT now()
);
CREATE TABLE app_sessions (
  token_hash STRING PRIMARY KEY,
  user_id INT NOT NULL REFERENCES app_users(id),
  expires_at TIMESTAMP NOT NULL
);

-- 1. register
INSERT INTO app_users (email, password_hash) VALUES ('sita@example.com', password_hash('correct horse battery'));
-- 2. log in: one row means the password is right
SELECT id FROM app_users WHERE email = 'sita@example.com' AND password_verify('correct horse battery', password_hash);
-- 3. make a token for the cookie; below, one fixed token stands for it, so the steps fit together
SELECT random_token() AS token;
INSERT INTO app_sessions VALUES (sha256('mR3v7yJ2nqKx0Qe8i4uX1t9LZbF5cH6aWgD2sP0oVkE'), 1, dateadd('d', 7, now()));
-- 4. every request: who is this?
SELECT u.id, u.email
FROM app_sessions s JOIN app_users u ON u.id = s.user_id
WHERE s.token_hash = sha256('mR3v7yJ2nqKx0Qe8i4uX1t9LZbF5cH6aWgD2sP0oVkE') AND s.expires_at > now();
-- 5. log out
DELETE FROM app_sessions WHERE token_hash = sha256('mR3v7yJ2nqKx0Qe8i4uX1t9LZbF5cH6aWgD2sP0oVkE');
SELECT count(*) AS sessions_left FROM app_sessions;

Result:

INSERT INTO app_users (email, password_hash) VALUES ('sita@example.com', '$pbkdf2-sha256$i=600000$cr

1 row(s) inserted

SELECT id FROM app_users WHERE email = 'sita@example.com' AND password_verify('correct horse battery

(1 row)

SELECT random_token() AS token

(1 row)

INSERT INTO app_sessions VALUES (sha256('mR3v7yJ2nqKx0Qe8i4uX1t9LZbF5cH6aWgD2sP0oVkE'), 1, dateadd('

1 row(s) inserted

SELECT u.id, u.email FROM app_sessions AS s JOIN app_users AS u ON u.id = s.user_id WHERE s.token_ha

(1 row)

DELETE FROM app_sessions WHERE token_hash = sha256('mR3v7yJ2nqKx0Qe8i4uX1t9LZbF5cH6aWgD2sP0oVkE')

1 row(s) deleted

SELECT count(*) AS sessions_left FROM app_sessions

(1 row)