> >>>>    - 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
Index: src/include/access/xact.h
===================================================================
RCS file: /home/ncvs/pgsql/pgsql-server/src/include/access/xact.h,v
retrieving revision 1.52
diff -u -r1.52 xact.h
--- src/include/access/xact.h   14 May 2003 03:26:03 -0000      1.52
+++ src/include/access/xact.h   30 Jul 2003 01:40:39 -0000
@@ -33,6 +33,7 @@
 /* Xact read-only state */
 extern bool    DefaultXactReadOnly;
 extern bool    XactReadOnly;
+extern bool    XactForceReadOnly;
 
 /*
  *     transaction states - transaction state from server perspective
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /home/ncvs/pgsql/pgsql-server/src/backend/utils/misc/guc.c,v
retrieving revision 1.144
diff -u -r1.144 guc.c
--- src/backend/utils/misc/guc.c        29 Jul 2003 00:03:18 -0000      1.144
+++ src/backend/utils/misc/guc.c        30 Jul 2003 04:39:20 -0000
@@ -94,6 +94,7 @@
 static const char *assign_log_error_verbosity(const char *newval, bool doit,
                                                   bool interactive);
 static bool assign_phony_autocommit(bool newval, bool doit, bool interactive);
+static bool assign_transaction_read_only(bool newval, bool doit, bool interactive);
 
 
 /*
@@ -814,6 +815,15 @@
                        GUC_NO_RESET_ALL | GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
                },
                &XactReadOnly,
+               false, assign_transaction_read_only, NULL
+       },
+       {
+               {"transaction_force_read_only", PGC_SUSET, CLIENT_CONN_STATEMENT,
+                       gettext_noop("Forces transactions that are in READ ONLY mode 
to stay READ ONLY"),
+                       NULL,
+                       GUC_NO_SHOW_ALL | GUC_NO_RESET_ALL | GUC_NOT_IN_SAMPLE
+               },
+               &XactForceReadOnly,
                false, NULL, NULL
        },
        {
@@ -4375,6 +4385,39 @@
                return false;
        }
        return true;
+}
+
+
+static bool
+assign_transaction_read_only(bool newval, bool doit, bool interactive)
+{
+       if (XactForceReadOnly == false)
+       {
+               if (doit == true)
+                       XactReadOnly = newval;
+               return true;
+       } else {
+               if (superuser() == false)
+               {
+                       if (newval == true)
+                       {
+                               if (doit)
+                                       XactReadOnly = true;
+
+                               return true;
+                       } else {
+                               if (doit && interactive)
+                                       ereport(ERROR,
+                                               
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+                                                errmsg("Insufficient privileges to 
SET transaction_read_only TO FALSE")));
+                               return false;
+                       }
+               } else {
+                       if (doit)
+                               XactReadOnly = newval;
+                       return true;
+               }
+       }
 }
 
 
Index: src/backend/access/transam/xact.c
===================================================================
RCS file: /home/ncvs/pgsql/pgsql-server/src/backend/access/transam/xact.c,v
retrieving revision 1.149
diff -u -r1.149 xact.c
--- src/backend/access/transam/xact.c   21 Jul 2003 20:29:39 -0000      1.149
+++ src/backend/access/transam/xact.c   30 Jul 2003 01:39:13 -0000
@@ -211,6 +211,7 @@
 
 bool           DefaultXactReadOnly = false;
 bool           XactReadOnly;
+bool           XactForceReadOnly = false;
 
 int                    CommitDelay = 0;        /* precommit delay in microseconds */
 int                    CommitSiblings = 5; /* number of concurrent xacts needed to

Attachment: pgp00000.pgp
Description: PGP signature

Reply via email to