PostgreSQL stuff

A colleague of mine got his template1 corrupted and I just told him to recreate it from template0. But there's a catch of course, template1 being a template database.

The quick fix for this is to connect as superuser, set template1 as not being a template database, drop and recreate the template1 from template0:

$ psql -U postgres postgres
postgres=# update pg_database set datistemplate = false where datname='template1';
UPDATE 1
postgres=# drop database template1;
DROP DATABASE
postgres=# create database template1 template=template0;
CREATE DATABASE
postgres=# update pg_database set datistemplate = true where datname='template1';
UPDATE 1
postgres=#

I also found an old, but not outdated, article by Josh Berkus about pretty much the same issue, explaining it more thorough, on the PostgreSQL Wiki.

Without any changes to template1 a sane default when creating a new database could be something like the script below.

It creates a newdatabase newdb with two users, adminuser for administration and rouser for read only access. (I just realized I forgot a rwuser but never mind.)

The only thing that bugs me is the tedious work of granting access to rouser when adding stuff to the database. Any takers on that one?

begin;
    create role dbname nosuperuser nocreatedb nocreaterole noinherit nologin;
    create role adminuser nosuperuser nocreatedb nocreaterole noinherit login encrypted password 'secretpassword';
    grant dbname to adminuser;
end;

create database dbname with owner=adminuser;

\connect dbname
begin;
    revoke all on database dbname from public;
    create role rouser nosuperuser nocreatedb nocreaterole noinherit login encrypted password 'secret';
    grant all on schema public to adminuser with grant option;
    revoke all on schema public from public;
    grant connect on database dbname to rouser;
    grant usage on schema public to rouser;
end;

\connect dbname adminuser
begin;
    create table account(id serial, created timestamp  default now(), name text not null, password text not null);
    grant select on account to rouser;
end;

-- drop database dbname;
-- drop role rouser;
-- drop role adminuser;
-- drop role dbname;