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?

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Miles Keaton <mileskeaton@gmail.com> writes:[color=blue]
> 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'));[/color]

There are similar functions in contrib/pgcrypto/, I believe.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

mileskeaton@gmail.com (Miles Keaton) writes:[color=blue]
> 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?[/color]

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.