universe@16: -- Create a database owner role, which is also a privileged user
universe@16: create user lightpit_dbo with password 'lpit_dbo_changeme';
universe@2: 
universe@2: -- Create the actual (unprivileged) database user
universe@2: create user lightpit_user with password 'lpit_user_changeme';
universe@2: 
universe@2: -- Create the LightPIT schema
universe@2: create schema lightpit authorization lightpit_dbo;
universe@5: grant usage on schema lightpit to lightpit_user;
universe@2: 
universe@2: -- Grant basic privileges to user (the granting user must be the dbo)
universe@2: alter default privileges for role lightpit_dbo in schema lightpit
universe@2:     grant select, insert, update, delete on tables to lightpit_user;
universe@2: alter default privileges for role lightpit_dbo in schema lightpit
universe@2:     grant usage, select on sequences to lightpit_user;
universe@2: alter default privileges for role lightpit_dbo in schema lightpit
universe@2:     grant execute on functions to lightpit_user;
universe@2: alter default privileges for role lightpit_dbo in schema lightpit
universe@2:     grant usage on types to lightpit_user;
universe@5: 
universe@5: -- restrict the search path to the lightpit schema
universe@5: alter role lightpit_dbo set search_path to lightpit;
universe@5: alter role lightpit_user set search_path to lightpit;