Re: [Gnumed-devel] Corrupt database

[Karsten Hilbert]

On Sun, Jun 02, 2013 at 12:48:14AM +0200, Jerzy Luszawski wrote:

> > > > > 2. How to drop a readonly table 
> > > > 
> > > > set default_transaction_read_only to off;
> > > > drop table ...;
> > > 
> > > If I understand this option correctly, using it is insecure, as any
> > > transaction (may be from all over the world) started before resetting
> > > it to ON becomes read-write.
> > 
> > *started before* ?   I can't believe that to be true.
> 
> ... started before RESETTING (this option) TO ON.

True -- but only with this session (as you say below).  This
would mean that someone would need to hijack my session (and
what for ? they can open their own read-write session) in
order to abuse *my* setting (as seems implied by "may be
from all over the world").

> > To my knowledge it affect transactions started *after*
> > issuing this WITHIN THIS CONNECTION ONLY.
> This is database-wide option and is preserved between
> connections, which you can easily test.

No it is not.

connect to default-read-only-DB
default is now read-only
set default_transaction_read_only to off;
default is now read-write
tx: is rw
tx: is rw
disconnect
connect to default-read-only-DB
default is now read-only
...

To make it stick across reconnects you will have to apply
ALTER DATABASE.

> Imagine the case when you have set default_transaction_read_only to OFF
> and have long running transaction. Until you reset
> default_transaction_read_only to ON at the end of your long
> transaction, every new transaction is RW.

That is correct. But I wonder what the problem with this is ?

New transactions after a new connect will, again, be
read-only (because that's how our database is configured).

> > > That's why I use 
> > > 
> > > set session characteristics as transaction READ WRITE ;
> > 
> > Nonetheless, this is the safer option, indeed !
> > 
> > (Because it applies to the one transaction one runs it in ONLY.)
> 
> Not transaction but SESSION. You can have many transactions within a
> session (connection).

Yes, seesion (as in connection). This is just another way of
saying set default_transaction_read_only to off.

Karsten
-- 
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346