By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,847 Members | 2,296 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,847 IT Pros & Developers. It's quick & easy.

Data Encryption in PostgreSQL, and a Tutorial.

P: n/a
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
Nov 23 '05 #1
Share this Question
Share on Google+
19 Replies


P: n/a
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

Nov 23 '05 #2

P: n/a
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.
+1-503-667-4564 - jd@commandprompt.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

Nov 23 '05 #3

P: n/a
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
Nov 23 '05 #4

P: n/a
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.

Nov 23 '05 #5

P: n/a
> 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

Nov 23 '05 #6

P: n/a
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.net
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
Nov 23 '05 #7

P: n/a
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*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #8

P: n/a
"scott.marlowe" <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*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #9

P: n/a
On Mon, 12 Apr 2004, Tom Lane wrote:
"scott.marlowe" <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

Nov 23 '05 #10

P: n/a
> True, but the original discussion, I believe, was on storing user
passwords etc... for which md5 is the preferred method...


I thought the original question was what to use for storing credit cards,
for which you want a decryptable method. (A public/private key method
would be even better for credit card data IMHO, but I don't think pgcrypto
includes one.)
--
Mike Nolan

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

Nov 23 '05 #11

P: n/a
sc***********@ihs.com ("scott.marlowe") wrote:
On Mon, 12 Apr 2004, Tom Lane wrote:
"scott.marlowe" <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...


No, the original discussion was about encrypting fields in the
database, so MD5 doesn't cut it.

Actually, for the purpose being pointed at, I would actually suggest
that the Gentle User consider preferring that the database DOESN'T
directly support encryption, because if it did, it would be tempting
to pass encryption keys to the database, thereby COMPROMISING the
security of the system.

After all, suppose the database supports stored procedures of the
form:

encrypt(key, field)
and
decrypt(key, field)

Then an unscrupulous sysadmin type could replace them with alternative
stored procedures that add in a couple of inserts...

insert into nefarious_schema.keep_keys (id, key) values (nextval('my_keying'), key);
insert into nefarious_schema.keep_field (id, field) values (currval('my_keying'), field);

The data can only remain truly secure in the database if encryption
and decryption don't even take place there.

It is all well and nifty to throw encryption tools into the database,
but this example quite clearly demonstrates that this is not a recipe
for _improving_ security of the system...
--
"cbbrowne","@","ntlug.org"
http://www3.sympatico.ca/cbbrowne/crypto.html
"Computers double in speed every 18 months or so, so any "exponential
time" problem can be solved in linear time by waiting the requisite
number of months for the problem to become solvable in one month and
then starting the computation." -- pr***@Sunburn.Stanford.EDU
Nov 23 '05 #12

P: n/a
I think, that all is about key management. You can store your data with
strong RSA encryption. On server you will have only public key and on
client PC private key.

it's not so easy to use, but it's more secure than symmetrical cipher.

miso

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

Nov 23 '05 #13

P: n/a
On Mon, 12 Apr 2004, Christopher Browne wrote:
sc***********@ihs.com ("scott.marlowe") wrote:
On Mon, 12 Apr 2004, Tom Lane wrote:
"scott.marlowe" <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...


No, the original discussion was about encrypting fields in the
database, so MD5 doesn't cut it.


OK, thanks. I think I got my threads cross-wired.
Actually, for the purpose being pointed at, I would actually suggest
that the Gentle User consider preferring that the database DOESN'T
directly support encryption, because if it did, it would be tempting
to pass encryption keys to the database, thereby COMPROMISING the
security of the system.


I agree completely. There's a new italian law that says that everything
in a database that's personal data has to be encrypted, and there was
another discussion on that. Did you see that one go by? Seems the law
isn't real clear on where encryption / decryption or key holding should
take place.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #14

P: n/a
On Sun, 11 Apr 2004, Jerry McBride wrote:
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....


let's see:

su -
cd /usr/local/src/postgresql-7.4.2
cd contrib/pgcrypto
make
make install

Total time taken: <30 seconds.

If that's a make or break deal for you on a database I'd hate to go car
buying with you.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #15

P: n/a
does any one know how to get an md5()-like hash function using pgcrypto
for postgresql 7.3 ? without upgrading to 7.4

Thanks for any input.

Dias

scott.marlowe wrote:
On Sun, 11 Apr 2004, Jerry McBride wrote:

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....

let's see:

su -
cd /usr/local/src/postgresql-7.4.2
cd contrib/pgcrypto
make
make install

Total time taken: <30 seconds.

If that's a make or break deal for you on a database I'd hate to go car
buying with you.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #16

P: n/a
I think that's what digest does. It doesn't appear to install in 7.4
since 7.4 has the md5 function. I don't have a 7.3 box to test it on
though...

On Tue, 11 May 2004, Dias Bantekas wrote:
does any one know how to get an md5()-like hash function using pgcrypto
for postgresql 7.3 ? without upgrading to 7.4

Thanks for any input.

Dias

scott.marlowe wrote:
On Sun, 11 Apr 2004, Jerry McBride wrote:

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....

let's see:

su -
cd /usr/local/src/postgresql-7.4.2
cd contrib/pgcrypto
make
make install

Total time taken: <30 seconds.

If that's a make or break deal for you on a database I'd hate to go car
buying with you.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

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

Nov 23 '05 #17

P: n/a
Dias Bantekas wrote:
does any one know how to get an md5()-like hash function using pgcrypto
for postgresql 7.3 ? without upgrading to 7.4

Thanks for any input.


SELECT encode(digest(v_password, 'md5'), 'hex');

hlk
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #18

P: n/a
Dias Bantekas wrote:
does any one know how to get an md5()-like hash function using
pgcrypto for postgresql 7.3 ? without upgrading to 7.4

Thanks for any input.


SELECT encode(digest(v_password, 'md5'), 'hex');


BTW,

/usr/share/pgsql/contrib/pgcrypto.sql

is the script that defines the encode and digest functions.

--Berend Tober


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

Nov 23 '05 #19

P: n/a
bt****@computer.org wrote:
Dias Bantekas wrote:

does any one know how to get an md5()-like hash function using
pgcrypto for postgresql 7.3 ? without upgrading to 7.4

Thanks for any input.


SELECT encode(digest(v_password, 'md5'), 'hex');

BTW,

/usr/share/pgsql/contrib/pgcrypto.sql

is the script that defines the encode and digest functions.

--Berend Tober


thanks hlk, that's exactly what I was looking for.
Now I can create a md5() function and do my job!

encode is a native PG string function, it is not defined in pgcrypto.
Dias
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #20

This discussion thread is closed

Replies have been disabled for this discussion.