Data Encryption in PostgreSQL, and a Tutorial.

[Mike Cox]

Has anyone created something like that for Postgresql? It would be
really handy to encrypt credit card numbers and other information so
it stays secure.

If no one has created anything such as this, I am going to code up
something quite soon, but if it already exists, there is no need for
me to reinvent the wheel, so speak up! It is a law in places such as
the EU that many types of data must be encrypted if the database is
compromised.

I will put up my solution in a few days if one does not exist. But
before I do that, I want to give a quick tutorial on how to create a
file that will create tables, views and other such essentials. Most
people who use PostgreSQL just type in the commands in PostgreSQL, but
that is not as easily portable or backed up as what I'm about to show
you!

1. open vi with a file.
2. Comments can be made as long as you add to slashes before the
line:
--this is a comment.
3. Next just type in the SQL commands you want!
4. after you are done, save the file.
5. then just do this to create the database you made in the file:
psql database_name < my_vi_file
6.That is it!

Here is a very simple sample of a file:

--This is a sample file. Use at your own risk. No Warranties
--Written by Mike Cox, author of the *nix "hm" command.

create table first(
MYNUMBER INTEGER);

create VIEW myview AS
select * from first;

--Ok this is the end. As you can see it is very simple and portable.
--Try it out. Here's how: psql your_database < this_file

[T. Relyea]

Mike Cox wrote:

Has anyone created something like that for Postgresql? It would be
really handy to encrypt credit card numbers and other information so
it stays secure.

If no one has created anything such as this, I am going to code up
something quite soon, but if it already exists, there is no need for
me to reinvent the wheel, so speak up! It is a law in places such as
the EU that many types of data must be encrypted if the database is
compromised.

I will put up my solution in a few days if one does not exist. But
before I do that, I want to give a quick tutorial on how to create a
file that will create tables, views and other such essentials. Most
people who use PostgreSQL just type in the commands in PostgreSQL, but
that is not as easily portable or backed up as what I'm about to show
you!

1. open vi with a file.
2. Comments can be made as long as you add to slashes before the
line:
--this is a comment.
3. Next just type in the SQL commands you want!
4. after you are done, save the file.
5. then just do this to create the database you made in the file:
psql database_name < my_vi_file
6.That is it!

Here is a very simple sample of a file:

--This is a sample file. Use at your own risk. No Warranties
--Written by Mike Cox, author of the *nix "hm" command.

create table first(
MYNUMBER INTEGER);

create VIEW myview AS
select * from first;

--Ok this is the end. As you can see it is very simple and portable.
--Try it out. Here's how: psql your_database < this_file

MySQL has encryption and decryption functions built in, doesn't Postgresql?

Todd

[Joshua D. Drake]

Hello,

Actually I would use psql with the \e option. This would allow you to do
what you suggest but also
allow you to stay within psql while you debug your statements. Then when
you are all done and
you have used the appropriate amount of COMMENT ON statements, you can
just do a pg_dump -s
and you are good to go.

Sincerely,

Joshua D. Drake
Mike Cox wrote:

Has anyone created something like that for Postgresql? It would be
really handy to encrypt credit card numbers and other information so
it stays secure.

If no one has created anything such as this, I am going to code up
something quite soon, but if it already exists, there is no need for
me to reinvent the wheel, so speak up! It is a law in places such as
the EU that many types of data must be encrypted if the database is
compromised.

I will put up my solution in a few days if one does not exist. But
before I do that, I want to give a quick tutorial on how to create a
file that will create tables, views and other such essentials. Most
people who use PostgreSQL just type in the commands in PostgreSQL, but
that is not as easily portable or backed up as what I'm about to show
you!

1. open vi with a file.
2. Comments can be made as long as you add to slashes before the
line:
--this is a comment.
3. Next just type in the SQL commands you want!
4. after you are done, save the file.
5. then just do this to create the database you made in the file:
psql database_name < my_vi_file
6.That is it!

Here is a very simple sample of a file:

--This is a sample file. Use at your own risk. No Warranties
--Written by Mike Cox, author of the *nix "hm" command.

create table first(
MYNUMBER INTEGER);

create VIEW myview AS
select * from first;

--Ok this is the end. As you can see it is very simple and portable.
--Try it out. Here's how: psql your_database < this_file

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
[phone] - jd@commandpromp t.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Christopher Browne]

In an attempt to throw the authorities off his trail, "T. Relyea" <no****@nospam. com> transmitted:

MySQL has encryption and decryption functions built in, doesn't Postgresql?

But of course.

See the "pgcrypto" contrib module in the source tree.

It is not typically compiled into what gets distributed with the
typical Linux/BSD distribution because of the library dependencies
that it forces in, as well as because the legalities surrounding the
distribution of cryptographic software vary from country to country,
making it potentially legally unsafe to ubiquitously include it.
--
let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;;
http://www.ntlug.org/~cbbrowne/spreadsheets.html
"If God meant us to be vegetarians why'd He make cows out of meat?"
-- seen on a bumper sticker

[mlw]

Mike Cox wrote:

Has anyone created something like that for Postgresql? It would be
really handy to encrypt credit card numbers and other information so
it stays secure.

Do you want to require a key to extract data or do you want to create an
encrypted data stream?

If you want to create an encrypted data element within the database that
requires a key, you would need to create a few functions, something like:

login(username varchar, passwd varchar, key cryptkey)
logout(username varchar)

You would also need to create a table of keys, something like this:

create table (username varchar, key cryptkey);

Lastly, one more set of functions for the various data types supported:

varchar decrypt(datum varchar)
int decrypt(datum int)

and

varchar crypt(..)
int crypt(...)
It is totally doable, and I'm not sure there is one out there. The only real
way to do it is public/private key encryption.

[Mike Nolan]

> Has anyone created something like that for Postgresql? It would be

really handy to encrypt credit card numbers and other information so
it stays secure.

Is there some reason you can't use contrib/pgcrypto? I use it
for storing passwords in an MD5 encryption and credit card data using
encrypt/decrypt, because I don't think it supports public/private
key encryption.
--
Mike Nolan

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Jerry McBride]

T. Relyea wrote:

Mike Cox wrote:

Has anyone created something like that for Postgresql? It would be
really handy to encrypt credit card numbers and other information so
it stays secure.

If no one has created anything such as this, I am going to code up
something quite soon, but if it already exists, there is no need for
me to reinvent the wheel, so speak up! It is a law in places such as
the EU that many types of data must be encrypted if the database is
compromised.

I will put up my solution in a few days if one does not exist. But
before I do that, I want to give a quick tutorial on how to create a
file that will create tables, views and other such essentials. Most
people who use PostgreSQL just type in the commands in PostgreSQL, but
that is not as easily portable or backed up as what I'm about to show
you!

1. open vi with a file.
2. Comments can be made as long as you add to slashes before the
line:
--this is a comment.
3. Next just type in the SQL commands you want!
4. after you are done, save the file.
5. then just do this to create the database you made in the file:
psql database_name < my_vi_file
6.That is it!

Here is a very simple sample of a file:

--This is a sample file. Use at your own risk. No Warranties
--Written by Mike Cox, author of the *nix "hm" command.

create table first(
MYNUMBER INTEGER);

create VIEW myview AS
select * from first;

--Ok this is the end. As you can see it is very simple and portable.
--Try it out. Here's how: psql your_database < this_file

MySQL has encryption and decryption functions built in, doesn't
Postgresql?

Todd

Obviously not... that's why we don't use it at work....

--

*************** *************** *************** *************** *************** ***
Registered Linux User Number 185956
http://groups.google.com/groups?hl=e...ff&group=linux
Join me in chat at #linux-users on irc.freenode.ne t
This email account no longers accepts attachments or messages containing
html.
12:26pm up 35 days, 13:39, 2 users, load average: 2.51, 2.56, 2.58

[scott.marlowe]

On Fri, 9 Apr 2004, Christopher Browne wrote:

In an attempt to throw the authorities off his trail, "T. Relyea" <no****@nospam. com> transmitted:

MySQL has encryption and decryption functions built in, doesn't Postgresql?

But of course.

See the "pgcrypto" contrib module in the source tree.

It is not typically compiled into what gets distributed with the
typical Linux/BSD distribution because of the library dependencies
that it forces in, as well as because the legalities surrounding the
distribution of cryptographic software vary from country to country,
making it potentially legally unsafe to ubiquitously include it.

I thought md5() was a built-in nowadays...
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

"scott.marl owe" <sc***********@ ihs.com> writes:

On Fri, 9 Apr 2004, Christopher Browne wrote:

See the "pgcrypto" contrib module in the source tree.

It is not typically compiled into what gets distributed with the
typical Linux/BSD distribution because of the library dependencies
that it forces in, as well as because the legalities surrounding the
distribution of cryptographic software vary from country to country,
making it potentially legally unsafe to ubiquitously include it.

I thought md5() was a built-in nowadays...

Yeah, it is, but md5 is not considered cryptography because it is not
reversible (you can't decrypt to get back what you put in). As such
it's not restricted under US munitions law, nor anyone else's that
I've heard of.

regards, tom lane

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

[scott.marlowe]

On Mon, 12 Apr 2004, Tom Lane wrote:

"scott.marl owe" <sc***********@ ihs.com> writes:

On Fri, 9 Apr 2004, Christopher Browne wrote:

See the "pgcrypto" contrib module in the source tree.

It is not typically compiled into what gets distributed with the
typical Linux/BSD distribution because of the library dependencies
that it forces in, as well as because the legalities surrounding the
distribution of cryptographic software vary from country to country,
making it potentially legally unsafe to ubiquitously include it.

I thought md5() was a built-in nowadays...

Yeah, it is, but md5 is not considered cryptography because it is not
reversible (you can't decrypt to get back what you put in). As such
it's not restricted under US munitions law, nor anyone else's that
I've heard of.

True, but the original discussion, I believe, was on storing user
passwords etc... for which md5 is the preferred method...
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings