FREE AI-Enhanced Online PostgreSQL Compiler

Untitled*

You must execute the code again to save those values in the database.

create extension pgcrypto;

create table user_passwords (username text, password bytea);

-- Here we rely on the default client_encoding; we don't control the exact bytes encrypted:
INSERT INTO user_passwords VALUES
  ('user1 (pure ASCII, don''t mind the encoding) ['||length('password1')||']', pgp_sym_encrypt('password1', 'key1')),
  ('Input as implicit UTF-8 (session''s default = UTF-8) ['||length('FrançoisCommeMonPrénom')||']', pgp_sym_encrypt('FrançoisCommeMonPrénom', 'key1'));
-- Newer versions of the app know that the input should be encrypted after having been converted to bytea using _the same encoding as what will be used on output_:
INSERT INTO user_passwords VALUES
  ('Input as explicit ISO8859-1 ['||length('Forcément')||']', pgp_sym_encrypt_bytea(convert_to('Forcément', 'iso8859-1'), 'key1'));

SELECT username "username with [chars]", length(pgp_sym_decrypt_bytea(password, 'key1')) bytes FROM user_passwords;

-- The front-end application knows that passwords from year 2000 have been encrypted as ISO8859-1 strings, so it pops them out explicitely:
SELECT
  username, convert_from(pgp_sym_decrypt_bytea(password, 'key1'), 'iso8859-1') 
FROM user_passwords;

username with [chars]	bytes
user1 (pure ASCII, don't mind the encoding) [9]	9
Input as implicit UTF-8 (session's default = UTF-8) [22]	24
Input as explicit ISO8859-1 [9]	9

username	convert_from
user1 (pure ASCII, don't mind the encoding) [9]	password1
Input as implicit UTF-8 (session's default = UTF-8) [22]	FranÃ§oisCommeMonPrÃ©nom
Input as explicit ISO8859-1 [9]	Forcément

CREATE EXTENSION
CREATE TABLE
INSERT 0 2
INSERT 0 1