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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
