Re: [PATCH] Re: 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: Christopher Browne <cbbrowne(at)libertyrms(dot)info>, pgsql-patches(at)postgresql(dot)org, Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>, Gavin Sherry <swm(at)linuxworld(dot)com(dot)au>, pgsql-advocacy(at)postgresql(dot)org
Subject: Re: [PATCH] Re: Why READ ONLY transactions?
Date: 2003-07-31 01:29:37
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-advocacy pgsql-hackers pgsql-patches

If we change default_transaction_read_only to PGC_USERLIMIT, the
administrator can turn it on and off, but an ordinary user can only turn
it on, but not off.

Would that help?


Sean Chittenden wrote:
-- Start of PGP signed section.
> > >>>> - Read only transactions, bringing a greater level of
> > >>>> security to web and enterprise applications by protecting
> > >>>> data from modification.
> >
> > >> This should be removed. Even though I added it to the press
> > >> release, I've just realised it's not really a security measure
> > >> against SQL injection since injected code can just specify 'SET
> > >> TRANSACTION READ WRITE'. We should still mention it, but not as a
> > >> security measure.
> >
> > > Aside from spec compliance, whats the bonus for having it then? Or
> > > put a better way, why/when would I want to use this?
> >
> > If I am writing a "report program" that isn't supposed to do any
> > updates to anything, then I would be quite happy to set things to
> > READ-ONLY as it means that I won't _accidentally_ do updates.
> >
> > It's like adding a pair of suspenders to your wardrobe. You can
> > _always_, if you really try, get your pants to fall down, but this
> > provides some protection.
> >
> > I would NOT call it a "security" provision, as it is fairly easily
> > defeated using SET TRANSACTION.
> 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:
> *) If the GUC transaction_force_read_only is set to FALSE, nothing
> changes in PostgreSQL's behavior. The default is FALSE, letting
> users change from READ ONLY to READ WRITE at will.
> *) If transaction_force_read_only is TRUE, this sandboxes the
> connection for the remainder of the connection if the session is
> set to read only. The following bits apply:
> a) if you're a super user, you can change transaction_read_only.
> b) if you're not a super user, you can change transaction_read_only
> to true.
> c) if you're not a super user, you can always change
> transaction_read_only from false to true. If
> transaction_force_read_only is true, you can't change
> transaction_read_only from true to false.
> d) If transaction_force_read_only is TRUE, but
> transaction_read_only is FALSE, the transaction is still READ
> e) Only super users can change transaction_force_read_only.
> Basically, if you want to permanently prevent a user from ever being
> able to get in a non-read only transaction, do:
> \c [dbname] [db_superuser]
> ALTER USER test SET default_transaction_read_only TO TRUE;
> ALTER USER test SET transaction_force_read_only TO TRUE;
> -- To test:
> regression=# \c regression test
> regression=> SHOW transaction_read_only;
> transaction_read_only
> -----------------------
> on
> (1 row)
> regression=> SHOW transaction_force_read_only;
> transaction_force_read_only
> -----------------------------
> on
> (1 row)
> regression=> SET transaction_read_only TO FALSE;
> ERROR: Insufficient privileges to SET transaction_read_only TO FALSE
> It's also possible to set transaction_force_read_only in
> postgresql.conf making it possible to create read only databases to
> non-superusers by starting postgresql with
> default_transaction_read_only and transaction_force_read_only set to
> TRUE. If this patch is well received, I'll quickly bang out some
> documentation for this new GUC. From a security stand point, this is
> a nifty feature. -sc
> --
> Sean Chittenden

[ Attachment, skipping... ]
-- End of PGP section, PGP failed!

Bruce Momjian |
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


Browse pgsql-advocacy by date

  From Date Subject
Next Message Bruce Momjian 2003-07-31 01:39:24 Re: What I would say if someone asked me about no win32
Previous Message Sean Chittenden 2003-07-30 23:42:31 Re: [PATCHES] [PATCH] Re: Why READ ONLY transactions?

Browse pgsql-hackers by date

  From Date Subject
Next Message Christopher Kings-Lynne 2003-07-31 01:37:04 Re: using adbin, conbin, etc.
Previous Message Christopher Kings-Lynne 2003-07-31 01:28:52 Re: using adbin, conbin, etc.

Browse pgsql-patches by date

  From Date Subject
Next Message Bruce Momjian 2003-07-31 01:43:22 Re: [PATCHES] [PATCH] Re: Why READ ONLY transactions?
Previous Message Joe Conway 2003-07-31 00:02:47 Re: hexadecimal to decimal