setup/postgres/psql_create_database.sql

Tue, 03 Aug 2021 15:10:43 +0200

author
Mike Becker <universe@uap-core.de>
date
Tue, 03 Aug 2021 15:10:43 +0200
changeset 212
c50da26a6d31
parent 16
4e0998805276
permissions
-rw-r--r--

#154 improves issue view

16
4e0998805276 adds DatabaseFacade
Mike Becker <universe@uap-core.de>
parents: 5
diff changeset
1 -- Create a database owner role, which is also a privileged user
4e0998805276 adds DatabaseFacade
Mike Becker <universe@uap-core.de>
parents: 5
diff changeset
2 create user lightpit_dbo with password 'lpit_dbo_changeme';
2
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
3
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
4 -- Create the actual (unprivileged) database user
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
5 create user lightpit_user with password 'lpit_user_changeme';
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
6
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
7 -- Create the LightPIT schema
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
8 create schema lightpit authorization lightpit_dbo;
5
131903fc16b8 moves psql DB setup script to another file (we want to support other engines later) + adds some missing statements to that file
Mike Becker <universe@uap-core.de>
parents: 2
diff changeset
9 grant usage on schema lightpit to lightpit_user;
2
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 -- 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
12 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
13 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
14 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
15 grant usage, select on sequences to lightpit_user;
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 execute on functions 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 on types to lightpit_user;
5
131903fc16b8 moves psql DB setup script to another file (we want to support other engines later) + adds some missing statements to that file
Mike Becker <universe@uap-core.de>
parents: 2
diff changeset
20
131903fc16b8 moves psql DB setup script to another file (we want to support other engines later) + adds some missing statements to that file
Mike Becker <universe@uap-core.de>
parents: 2
diff changeset
21 -- restrict the search path to the lightpit schema
131903fc16b8 moves psql DB setup script to another file (we want to support other engines later) + adds some missing statements to that file
Mike Becker <universe@uap-core.de>
parents: 2
diff changeset
22 alter role lightpit_dbo set search_path to lightpit;
131903fc16b8 moves psql DB setup script to another file (we want to support other engines later) + adds some missing statements to that file
Mike Becker <universe@uap-core.de>
parents: 2
diff changeset
23 alter role lightpit_user set search_path to lightpit;

mercurial