gistfile1.txt

-- 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;

-- admins
create role fred login in role appadmin,appuser;
create role jim login in role appadmin,appuser;

-- non-admins
create role sheila login in role appuser;


-- fred creates some things
\c - fred
set role appowner;
create table app.table1 (a serial);

-- jim creates some things
\c - jim
set role appowner;
create table app.table2 (b serial);

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

-- sheila can't create things in app.*:
\c - sheila
create table app.table3 (a integer);

-- but can select and insert:
insert into app.table1 default values;
insert into app.table2 default values;
select * from app.table1, app.table2;