PostgreSQL stuff

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;