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 |
Message-ID: | 200307310129.h6V1TbW07879@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
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
> 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
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? |
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. |
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 |