Theoretical maintenance mode for Rails using database roles.

rails_maintenance_with_db_roles.md

Database setup

-- NOTE: this script assumes a connection to a database
-- named `myapp_development` with adequate permissions
BEGIN;

-- user to be listed in config/database.yml
-- NOTE: this user does not own any database objects
CREATE USER myapp_user;


-- role for read-only access
CREATE ROLE myapp_development_read;

GRANT CONNECT
ON DATABASE myapp_development
TO myapp_development_read;

GRANT SELECT
ON ALL TABLES IN SCHEMA public
TO myapp_development_read;

GRANT SELECT
ON ALL SEQUENCES IN SCHEMA public
TO myapp_development_read;

GRANT myapp_development_read
TO myapp_user;


-- role for write access
CREATE ROLE myapp_development_write;

GRANT ALL PRIVILEGES
ON ALL TABLES IN SCHEMA public
TO myapp_development_write;

GRANT ALL PRIVILEGES
ON ALL SEQUENCES IN SCHEMA public
TO myapp_development_write;

GRANT myapp_development_write
TO myapp_user;


-- apply the above object-level permissions to future objects
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES
TO myapp_development_read;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON SEQUENCES
TO myapp_development_read;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL PRIVILEGES ON TABLES
TO myapp_development_write;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL PRIVILEGES ON SEQUENCES
TO myapp_development_write;

-- assuming no errors...
-- COMMIT;

Rails app

In ApplicationController:

  rescue_from PG::InsufficientPrivilege do |exception|
    respond_to do |format|
      format.html { render '/pages/maintenance', status: :service_unavailable }
    end
  end

Usage

To enable maitenance mode:

REVOKE myapp_development_write
FROM myapp_user;

To resume normal operations:

GRANT myapp_development_write
TO myapp_user;

Reset database to former state

-- NOTE: this script assumes a connection to a database
-- named `myapp_development` with adequate permissions
BEGIN;

-- clear default privileges
ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE SELECT ON TABLES
FROM myapp_development_read;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE SELECT ON SEQUENCES
FROM myapp_development_read;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE ALL PRIVILEGES ON TABLES
FROM myapp_development_write;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE ALL PRIVILEGES ON SEQUENCES
FROM myapp_development_write;


-- role for write access
REVOKE ALL PRIVILEGES
ON ALL TABLES IN SCHEMA public
FROM myapp_development_write;

REVOKE ALL PRIVILEGES
ON ALL SEQUENCES IN SCHEMA public
FROM myapp_development_write;

REVOKE myapp_development_write
FROM myapp_user;

DROP ROLE myapp_development_write;


-- role for read-only access
REVOKE CONNECT
ON DATABASE myapp_development
FROM myapp_development_read;

REVOKE SELECT
ON ALL TABLES IN SCHEMA public
FROM myapp_development_read;

REVOKE SELECT
ON ALL SEQUENCES IN SCHEMA public
FROM myapp_development_read;

REVOKE myapp_development_read
FROM myapp_user;

DROP ROLE myapp_development_read;


-- user previously listed in config/database.yml
-- NOTE: this user should not own any database objects
DROP USER myapp_user;


-- assuming no errors...
-- COMMIT;