Re: [PATCH] Re: [pgsql-advocacy] Why READ ONLY transactions?

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Sean Chittenden <sean(at)chittenden(dot)org>
Cc: Josh Berkus <josh(at)agliodbs(dot)com>, pgsql-patches(at)postgresql(dot)org
Subject: Re: [PATCH] Re: [pgsql-advocacy] Why READ ONLY transactions?
Date: 2003-12-01 04:10:09
Message-ID: 200312010410.hB14A9t05973@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-advocacy pgsql-hackers pgsql-patches

Sean Chittenden wrote:
> > > > Um, why not make it an actual full blown security feature by
> > > > applying the following patch? This gives PostgreSQL real read
> > > > only transactions that users can't escape from. Notes about the
> > > > patch:
> > >
> > > Way nifty.
> > >
> > > I vote in favor of this patch (suitably documented & debugged) for 7.5.
> >
> > Heh, there ain't much to debug: it's pretty straight forward. I ran
> > all the use cases/syntaxes I could think of and they worked as
> > expected. It's a pretty chump little ditty that I originally wrote
> > for the sake of the 7.4 PR, but it's proving to be quite useful here
> > in my tree... though I like the name "jail_read_only_transactions"
> > more... patch updated for new name.
>
> Err.. and attached. -sc

I assume this patch is to control this way of breaking out of a
read-only transaction:

test=> START TRANSACTION READ ONLY;
START TRANSACTION
test=> CREATE TABLE x(y INT);
ERROR: transaction IS read-only
test=> COMMIT;
COMMIT

test=> START TRANSACTION READ ONLY;
START TRANSACTION
test=> SET transaction_read_only = FALSE;
SET
test=> CREATE TABLE x (y INT);
CREATE TABLE
test=> COMMIT;
COMMIT

This seems like a valuable feature, as others have mentioned. However,
should it also prevent changes to default_transaction_read_only?

What is the use case for this functionality?

Seems someone could easily break out of this by doing:

test=> START TRANSACTION READ ONLY;
START TRANSACTION
test=> COMMIT;
COMMIT

test=> START TRANSACTION;
START TRANSACTION
test=> CREATE TABLE x (y INT);
CREATE TABLE

This shows that default_transaction_read_only probably has to be
restricted too by the same variable.

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

In response to

Responses

Browse pgsql-advocacy by date

  From Date Subject
Next Message Bruce Momjian 2003-12-01 04:52:10 Re: ISO 8601 "Time Intervals" of the "format with time-unit
Previous Message Bruce Momjian 2003-12-01 02:58:20 Re: Call from Info World

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2003-12-01 04:11:45 Re: Wrong masklen in result of netmask()/hostmask()
Previous Message Tom Lane 2003-12-01 04:06:55 Wrong masklen in result of netmask()/hostmask()

Browse pgsql-patches by date

  From Date Subject
Next Message Bruce Momjian 2003-12-01 04:48:04 Re: Numeric version of factorial()
Previous Message Joe Conway 2003-12-01 04:04:12 Re: Problem with dblink