Thread: how to encode/encrypt a string

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?

Miles Keaton <mileskeaton@gmail.com> writes:
> 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'));

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

            regards, tom lane

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.