Re: [PATCH] Re: [pgsql-advocacy] 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@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