Thread: Data Encryption in PostgreSQL, and a Tutorial.
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
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
In an attempt to throw the authorities off his trail, "T. Relyea" <nospam@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
> 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
On Fri, 9 Apr 2004, Christopher Browne wrote: > In an attempt to throw the authorities off his trail, "T. Relyea" <nospam@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...
"scott.marlowe" <scott.marlowe@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
On Mon, 12 Apr 2004, Tom Lane wrote: > "scott.marlowe" <scott.marlowe@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...
> 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
scott.marlowe@ihs.com ("scott.marlowe") wrote:
> On Mon, 12 Apr 2004, Tom Lane wrote:
>
>> "scott.marlowe" <scott.marlowe@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." -- pratt@Sunburn.Stanford.EDU
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
On Mon, 12 Apr 2004, Christopher Browne wrote:
> scott.marlowe@ihs.com ("scott.marlowe") wrote:
> > On Mon, 12 Apr 2004, Tom Lane wrote:
> >
> >> "scott.marlowe" <scott.marlowe@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.
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=en&safe=off&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
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.
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 >
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 >
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
> 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
btober@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