getGeneratedKeys()

On Fri, 2005-04-01 at 10:05 +1000, Jamie Deppeler wrote:

Hi to all,

I have one problem with PostgreSQL and Java. I have a table with Primary
key(serial) field, but after I insert a record i am unable to retrieve
this value. I have tried getGeneratedKeys() and i get nothing returned.

I am not a Java programmer but normally I would use select currval
('sequencename');

There is another method to retrieve this field?

thanks

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

http://archives.postgresql.org

--
Command Prompt, Inc., Your PostgreSQL solutions company. 503-667-4564
Custom programming, 24x7 support, managed services, and hosting
Open Source Authors: plPHP, pgManage, Co-Authors: plPerlNG
Reliable replication, Mammoth Replicator - http://www.commandprompt.com/

I was talking to someone just recently who was saying that they
were thinking about going with Oracle rather than Postgresql
because Oracle has a their story in place about how to do
disk encryption. So I am of course, looking into how to do it
with Postgresql...

(As to why you would *care* about disk encryption, I would guess
the scenario is you've got a bunch of guys in the back room
hot-swapping RAID drives, and you'd rather not post armed guards
there to watch what happens to the older units.)

contrib/pgcrypto looks pretty interesting, but I gather it's
intended to let you encrypt particular fields inside a database,
rather than the whole ball of wax.

Maybe the right way to do it is to just get the OS to encrypt
everything, and not make postgresql jump through any extra hoops?
I see there's a general Linux disk encryption FAQ out there:

http://www.telenovela-world.com/~spade/linux/howto/Disk-Encryption-HOWTO/index.html

Doing some searches of the archives, I haven't turned up much
discussion more recent than about a year ago, e.g.

http://archives.postgresql.org/pgsql-admin/2004-03/msg00049.php

Is there anything new on this front?

Joseph Brenner <doom@kzsu.stanford.edu> writes:

I was talking to someone just recently who was saying that they
were thinking about going with Oracle rather than Postgresql
because Oracle has a their story in place about how to do
disk encryption. So I am of course, looking into how to do it
with Postgresql...

Maybe the right way to do it is to just get the OS to encrypt
everything, and not make postgresql jump through any extra hoops?

Yup, that's what we recommend. It's more complete and more bulletproof
than anything we'd be likely to invent for ourselves.

regards, tom lane

On Fri, Apr 01, 2005 at 13:43:01 -0800,
Joseph Brenner <doom@kzsu.stanford.edu> wrote:

(As to why you would *care* about disk encryption, I would guess
the scenario is you've got a bunch of guys in the back room
hot-swapping RAID drives, and you'd rather not post armed guards
there to watch what happens to the older units.)

You really do need some sort of threat model (or government regulations
that say what you need to do regardless) before you can make good decisions
about security.

Maybe the right way to do it is to just get the OS to encrypt
everything, and not make postgresql jump through any extra hoops?

Doing it in the OS is one possibility. That can work well against people
stealing your hardware. It won't work so well in keeping sysadmins from
snooping on your database.

In the last exciting episode, doom@kzsu.stanford.edu (Joseph Brenner) wrote:

I was talking to someone just recently who was saying that they
were thinking about going with Oracle rather than Postgresql
because Oracle has a their story in place about how to do
disk encryption. So I am of course, looking into how to do it
with Postgresql...

(As to why you would *care* about disk encryption, I would guess
the scenario is you've got a bunch of guys in the back room
hot-swapping RAID drives, and you'd rather not post armed guards
there to watch what happens to the older units.)

contrib/pgcrypto looks pretty interesting, but I gather it's
intended to let you encrypt particular fields inside a database,
rather than the whole ball of wax.

Maybe the right way to do it is to just get the OS to encrypt
everything, and not make postgresql jump through any extra hoops?
I see there's a general Linux disk encryption FAQ out there:

http://www.telenovela-world.com/~spade/linux/howto/Disk-Encryption-HOWTO/index.html

Doing some searches of the archives, I haven't turned up much
discussion more recent than about a year ago, e.g.

http://archives.postgresql.org/pgsql-admin/2004-03/msg00049.php

Is there anything new on this front?

If your threat model indicates that encrypting data at the disk level
represents protection against some attack involving theft of disk
drives, you would presumably find that using some form of OS loopback
device with a crypto layer to be useful, and that would not require
any particular support from PostgreSQL. Note that this model cannot
protect against threats from system administrators as, in order for
them to mount the filesystems, they must have access to the crypto
keys. Furthermore, it cannot protect _at all_ against attacks that
can take place while the database is up and running.

A second approach, using pgcrypto, requires that you entrust the
database process, and hence anyone with access to the relevant Unix
user, with the cryptographic keys. That can allow some portions of
the data to be encrypted, and others to remain plain text, and may
again be suitable if you trust the system administrators with the
keys. It has the merit that the sensitive data stays encrypted on
disk at all times; it is only in plain text form in memory and
possibly as it is being transmitted between server and client (protect
against that using SSL connections).

A third approach is for the cryptographic layer to stay purely on the
application/client side. Encrypted data is encrypted on the client
side, and is only ever decrypted there. If you have any reason to be
concerned about threats that target the server, then you must not
trust either of the first two approaches, but must look to client-side
processing. Google for _Translucent Databases_ for more on this
approach...
--
output = ("cbbrowne" "@" "gmail.com")
http://linuxdatabases.info/info/slony.html
They are called computers simply because computation is the only
significant job that has so far been given to them. -- Louis Ridenour

Wow, nice analysis. Should this be in our documentation somewhere?

---------------------------------------------------------------------------

Christopher Browne wrote:

In the last exciting episode, doom@kzsu.stanford.edu (Joseph Brenner) wrote:

I was talking to someone just recently who was saying that they
were thinking about going with Oracle rather than Postgresql
because Oracle has a their story in place about how to do
disk encryption. So I am of course, looking into how to do it
with Postgresql...

(As to why you would *care* about disk encryption, I would guess
the scenario is you've got a bunch of guys in the back room
hot-swapping RAID drives, and you'd rather not post armed guards
there to watch what happens to the older units.)

contrib/pgcrypto looks pretty interesting, but I gather it's
intended to let you encrypt particular fields inside a database,
rather than the whole ball of wax.

Maybe the right way to do it is to just get the OS to encrypt
everything, and not make postgresql jump through any extra hoops?
I see there's a general Linux disk encryption FAQ out there:

http://www.telenovela-world.com/~spade/linux/howto/Disk-Encryption-HOWTO/index.html

Doing some searches of the archives, I haven't turned up much
discussion more recent than about a year ago, e.g.

http://archives.postgresql.org/pgsql-admin/2004-03/msg00049.php

Is there anything new on this front?

If your threat model indicates that encrypting data at the disk level
represents protection against some attack involving theft of disk
drives, you would presumably find that using some form of OS loopback
device with a crypto layer to be useful, and that would not require
any particular support from PostgreSQL. Note that this model cannot
protect against threats from system administrators as, in order for
them to mount the filesystems, they must have access to the crypto
keys. Furthermore, it cannot protect _at all_ against attacks that
can take place while the database is up and running.

A second approach, using pgcrypto, requires that you entrust the
database process, and hence anyone with access to the relevant Unix
user, with the cryptographic keys. That can allow some portions of
the data to be encrypted, and others to remain plain text, and may
again be suitable if you trust the system administrators with the
keys. It has the merit that the sensitive data stays encrypted on
disk at all times; it is only in plain text form in memory and
possibly as it is being transmitted between server and client (protect
against that using SSL connections).

A third approach is for the cryptographic layer to stay purely on the
application/client side. Encrypted data is encrypted on the client
side, and is only ever decrypted there. If you have any reason to be
concerned about threats that target the server, then you must not
trust either of the first two approaches, but must look to client-side
processing. Google for _Translucent Databases_ for more on this
approach...
--
output = ("cbbrowne" "@" "gmail.com")
http://linuxdatabases.info/info/slony.html
They are called computers simply because computation is the only
significant job that has so far been given to them. -- Louis Ridenour

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

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Martha Stewart called it a Good Thing when pgman@candle.pha.pa.us (Bruce Momjian) wrote:

Wow, nice analysis. Should this be in our documentation somewhere?

Suggest a suitable section and I'd be more than happy to send in a
patch adding this in. The only place I see pgcrypto referred to
(which ought to be a pointer to "right place") is in the release
notes. Or perhaps this is an item for FAQ.html?
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxfinances.info/info/slony.html
REALITY is an illusion that stays put.

Christopher Browne wrote:

Martha Stewart called it a Good Thing when pgman@candle.pha.pa.us (Bruce Momj$

Wow, nice analysis. Should this be in our documentation somewhere?

Suggest a suitable section and I'd be more than happy to send in a
patch adding this in. The only place I see pgcrypto referred to
(which ought to be a pointer to "right place") is in the release
notes. Or perhaps this is an item for FAQ.html?

I think Server Run-Time environment is the proper place, perhaps as a
new section at the end:

http://www.postgresql.org/docs/8.0/interactive/runtime.html

I think your three approaches of filesystem-based, column-based, and
client-side based is a good analysis that should be in the
documentation. It isn't 100% PostgreSQL-specific, but showing how
pgcrypto, SSL, and encrypted file systems fit together is helpful. In
fact our MD5-encryption of the stored password is another relivant issue.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073