Mercurial > hg > LightPIT / file revision

 -- Create a database owner role, which is also a privileged user

 create user lightpit_dbo with password 'lpit_dbo_changeme';

 -- Create the actual (unprivileged) database user

 create user lightpit_user with password 'lpit_user_changeme';

 -- Create the LightPIT schema

 create schema lightpit authorization lightpit_dbo;

 grant usage on schema lightpit to lightpit_user;

 -- Grant basic privileges to user (the granting user must be the dbo)

 alter default privileges for role lightpit_dbo in schema lightpit

     grant select, insert, update, delete on tables to lightpit_user;

 alter default privileges for role lightpit_dbo in schema lightpit

     grant usage, select on sequences to lightpit_user;

 alter default privileges for role lightpit_dbo in schema lightpit

     grant execute on functions to lightpit_user;

 alter default privileges for role lightpit_dbo in schema lightpit

     grant usage on types to lightpit_user;

 -- restrict the search path to the lightpit schema

 alter role lightpit_dbo set search_path to lightpit;

 alter role lightpit_user set search_path to lightpit;