gistfile1.txt

-- do this stuff as postgres
\c - postgres

-- this role will own all the objects
create role appowner;
create schema app authorization appowner;

-- this is the admin group, which grants the admins the ability to
-- adopt the owner role for admin tasks. The noinherit means that the
-- admins don't get any other permissions from the owner.

create role appadmin noinherit in role appowner;

-- this role is for users who will query the tables
create role appuser;

grant usage on schema app to appuser;

alter default privileges for role appowner in schema app
  grant select,insert,update,delete on tables to appuser;

alter default privileges for role appowner in schema app
  grant usage on sequences to appuser;


-- this is the actual login user
create role fred login in role appuser;

-- make fred a DBA for the app temporarily:
grant appadmin to fred;
alter role fred set role = 'appowner';

-- fred does stuff
\c - fred
create table app.table1 (a serial);
create table app.table2 (b serial);

\c - postgres
-- remove the DBA stuff from fred:
alter role fred reset role;
revoke appadmin from fred;

-- now fred is just a user:
\c - fred
drop table app.table1;  -- fails
insert into app.table1 default values;
insert into app.table2 default values;
select * from app.table1, app.table2;

-- show ownership etc.
\dt+ app.*
\ds+ app.*