How do I enable READ-only (passive) mode in a particular RDBMS?

Question

As most of you have noticed all StackExchange sites have been under maintenance/recovery a few days ago so they switched on READ-only mode. From their blog I know they used to use MS SQL Server 2008, and recently have moved on to SQL 2012.

The question is how do you switch READ-only mode in a specific RDBMS, thus keep the service up during maintenance?

Since I use CUBRID RDBMS most of the time, I know they provide an ACCESS_MODE switch in CUBRID Broker middleware (cubrid_broker.conf) which allows DBAs to dynamically turn a READ/WRITE-enabled service to READ-only (+ ...) and vice versa. This is especially useful in an HA environment.

In MySQL as I know, even if you turn read_only=1 in my.cnf/my.ini, users with SUPER and replication privileges still can write to a read only server.

I wonder how this is done in other relational databases. How does SO do this?

Answer 1

I'm going to attempt to answer this question for Oracle.

Historically Oracle has never supported read-only databases.

It's possible to make a tablespace read-only: http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces006.htm

Individual tables can be made read-only: http://www.oracle-base.com/articles/11g/read-only-tables-11gr1.php

Or it's possible to make a select-only role and assign that to the user who logs in rather than the normal readwrite-role.

Answer 2

In SQL Server it's:

ALTER DATABASE <dbname> SET READ_ONLY;

But a full solution is going to rely on the application as well if you want it to be unobtrusive to the user. Most applications will be logging data all the time, even if the user isn't explicitly 'writing' to the db (The 'Viewed' stat on SE pages being an obvious example) So just setting the database to read only will probably end up with lots of errors being thrown back to the user, unless the app has been written to cope with this.

Answer 3

Some PostgreSQL solutions:

1. Create ReadOnly user and connect to database using it.

   CREATE OR REPLACE FUNCTION create_ro_user( in_username TEXT, in_password TEXT ) RETURNS VOID AS $BODY$
     BEGIN
       IF EXISTS ( SELECT 7 FROM pg_user WHERE usename = in_username ) THEN
         RAISE NOTICE 'user [%] already exists', in_username;
       ELSE
         EXECUTE 'CREATE USER ' || quote_ident(in_username) || ' WITH PASSWORD ' || quote_literal(in_password);
       END IF;
   /* schemas */
       PERFORM execute( 'GRANT USAGE ON SCHEMA ' || nspname || ' TO ' || in_username )
       FROM pg_namespace
       WHERE nspname !~ '^_'
         AND nspname !~ '^pg_'
         AND nspname != 'information_schema';
   /* tables and views */
       PERFORM execute( 'GRANT SELECT ON TABLE ' || table_schema || '."' || table_name || '" TO ' || in_username )
       FROM information_schema.tables
       WHERE table_schema !~ '^_'
         AND table_schema !~ '^pg_'
         AND table_schema != 'information_schema';
     END;
   $BODY$ LANGUAGE 'plpgsql' STRICT;  
   

   EDIT:

2. REVOKE privileges from current application user:

   REVOKE CREATE ON SCHEMA public FROM PUBLIC;
   REVOKE USAGE ON SCHEMA public FROM PUBLIC;
   REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public from app_user;
   GRANT USAGE ON SCHEMA public TO app_user;
   GRANT SELECT ON ALL TABLES IN SCHEMA public to app_user;   
   

   note: I didn't test it so please correct this if there should be done something more or something else. You have to check if it will work when user have active connection to db already.

3. You can also set default_transaction_read_only=on on connection by default, but it can by switched to off in session. More here: postgresql 9.1 doc and here: postgresql 9.1 doc