Re: how to encode/encrypt a string - Mailing list pgsql-general
From | Chris Browne |
---|---|
Subject | Re: how to encode/encrypt a string |
Date | |
Msg-id | 603c0zskte.fsf@dev6.int.libertyrms.info Whole thread Raw |
In response to | how to encode/encrypt a string (Miles Keaton <mileskeaton@gmail.com>) |
List | pgsql-general |
mileskeaton@gmail.com (Miles Keaton) writes: > still doing my switch from MySQL to PgSQL, and can't figure out what > the comparable function would be for this: > > In MySQL, to store a big secret (like a credit card number) in the > database that I didn't want anyone to be able to see without knowing > the salt/password value, I would do this into a blob-type field: > > INSERT INTO clients(ccnum) VALUES (ENCODE(''433904123121309319', 'xyzzy')); > > Then it would be stored as binary jumble in the database, only able > to be decoded with my "xyzzy" password. > > SELECT DECODE(ccnum, 'xyzzy') FROM clients; > > How would I do this same thing in PostgreSQL? There's a contrib module called pgcrypto; according to the README: encrypt(data::bytea, key::bytea, type::text)::bytea decrypt(data::bytea, key::bytea, type::text)::bytea encrypt_iv(data::bytea, key::bytea, iv::bytea, type::text)::bytea decrypt_iv(data::bytea, key::bytea, iv::bytea, type::text)::bytea Encrypt/decrypt data with cipher, padding data if needed. Pseudo-noteup: algo ['-' mode] ['/pad:' padding] Supported algorithms: bf - Blowfish aes, rijndael - Rijndael-128 Others depend on library and are not tested enough, so play on your own risk. Modes: 'cbc' (default), 'ecb'. Again, library may support more. Padding is 'pkcs' (default), 'none'. 'none' is mostly for testing ciphers, you should not need it. So, example: encrypt(data, 'fooz', 'bf') is equal to encrypt(data, 'fooz', 'bf-cbc/pad:pkcs') IV is initial value for mode, defaults to all zeroes. It is ignored for ECB. It is clipped or padded with zeroes if not exactly block size. If you're compiling PostgreSQL yourself, just head to the contrib/pgcrypto directory and type "make install" to install the relevant bits; you'll need to load 'pgcrypto.sql' in order to have access to the functions. The reasons why this is likely not included by default include: a) There are jurisdictions in which the use of cryptography requires permission from the local government; PostgreSQL has no treaty with governments, so cannot safely assume that distributing crypto-enabled software is actually legal. b) Compiling these additions requires additional external libraries that you may or may not have installed in suitable form. Forcing those dependancies would be unkind, particularly if it is possible that distributing cryptographic software is illegal in some jurisdictions... -- let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];; http://www3.sympatico.ca/cbbrowne/multiplexor.html Despite the high cost of living, it remains very popular.
pgsql-general by date: