setup/01_create_database.sql

Sun, 29 Oct 2017 13:35:43 +0100

author
mike@uapl02
date
Sun, 29 Oct 2017 13:35:43 +0100
changeset 3
acf54d3fa1d1
parent 2
fcb452578142
permissions
-rw-r--r--

switched to tomcat 8.5 (should be developed this way for maximum compatibility)

2
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
1 -- Create a database owner role, which has no login permissions.
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
2 -- You can either:
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
3 -- 1) login as default user and switch the user
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
4 -- 2) decide to override this decision and give login permissions
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
5 -- 3) use your superuser of choice to manage the database (not recommended!)
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
6 create role lightpit_dbo with password 'lpit_dbo_changeme';
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
7
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
8 -- Create the actual (unprivileged) database user
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
9 create user lightpit_user with password 'lpit_user_changeme';
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
10
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
11 -- Create the LightPIT schema
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
12 create schema lightpit authorization lightpit_dbo;
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
13
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
14
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
15 -- Grant basic privileges to user (the granting user must be the dbo)
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
16 alter default privileges for role lightpit_dbo in schema lightpit
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
17 grant select, insert, update, delete on tables to lightpit_user;
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
18 alter default privileges for role lightpit_dbo in schema lightpit
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
19 grant usage, select on sequences to lightpit_user;
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
20 alter default privileges for role lightpit_dbo in schema lightpit
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
21 grant execute on functions to lightpit_user;
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
22 alter default privileges for role lightpit_dbo in schema lightpit
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
23 grant usage on types to lightpit_user;

mercurial