|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?|
|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;
> (1 row)
> regression=> SHOW transaction_force_read_only;
> (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
|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?|
|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.|
|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|