setup/postgres/psql_create_database.sql

Mon, 09 Aug 2021 16:58:38 +0200

author
Mike Becker <universe@uap-core.de>
date
Mon, 09 Aug 2021 16:58:38 +0200
changeset 218
0e4a9b6f7a12
parent 16
4e0998805276
permissions
-rw-r--r--

Added tag v0.6 for changeset a5ad7cd5d000

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

mercurial