[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] how to encode/encrypt a string
From:       Chris Browne <cbbrowne () acm ! org>
Date:       2004-09-30 17:55:41
Message-ID: 603c0zskte.fsf () dev6 ! int ! libertyrms ! info
[Download RAW message or body]

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. 

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
[prev in list] [next in list] [prev in thread] [next in thread]