setup/postgres/psql_create_database.sql@1a0ac419f714
setup/postgres/psql_create_database.sql
Sat, 31 Mar 2018 18:11:09 +0200
- author
- Mike Becker <universe@uap-core.de>
- date
- Sat, 31 Mar 2018 18:11:09 +0200
- changeset 19
- 1a0ac419f714
- parent 16
- 4e0998805276
- permissions
- -rw-r--r--
removes the privileged data source from the application and the ability to have a web UI for a setup
This is a permanent decision: setups should be performed on the server by some admin. There is too much, which could go wrong and we have little chance to catch anything within a web UI.
1 -- Create a database owner role, which is also a privileged user
2 create user lightpit_dbo with password 'lpit_dbo_changeme';
4 -- Create the actual (unprivileged) database user
5 create user lightpit_user with password 'lpit_user_changeme';
7 -- Create the LightPIT schema
8 create schema lightpit authorization lightpit_dbo;
9 grant usage on schema lightpit to lightpit_user;
11 -- Grant basic privileges to user (the granting user must be the dbo)
12 alter default privileges for role lightpit_dbo in schema lightpit
13 grant select, insert, update, delete on tables to lightpit_user;
14 alter default privileges for role lightpit_dbo in schema lightpit
15 grant usage, select on sequences to lightpit_user;
16 alter default privileges for role lightpit_dbo in schema lightpit
17 grant execute on functions to lightpit_user;
18 alter default privileges for role lightpit_dbo in schema lightpit
19 grant usage on types to lightpit_user;
21 -- restrict the search path to the lightpit schema
22 alter role lightpit_dbo set search_path to lightpit;
23 alter role lightpit_user set search_path to lightpit;
