Re: [PATCH] Re: Why READ ONLY transactions?

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
> WRITE.
>
> 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]
> BEGIN;
> ALTER USER test SET default_transaction_read_only TO TRUE;
> ALTER USER test SET transaction_force_read_only TO TRUE;
> COMMIT;
>
> -- 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 | 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