Sun, 26 Nov 2017 16:51:57 +0100
moves psql DB setup script to another file (we want to support other engines later) + adds some missing statements to that file
setup/01_create_database.sql | file | annotate | diff | comparison | revisions | |
setup/postgres/psql_create_database.sql | file | annotate | diff | comparison | revisions |
1.1 --- a/setup/01_create_database.sql Sun Nov 26 16:20:38 2017 +0100 1.2 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 1.3 @@ -1,23 +0,0 @@ 1.4 --- Create a database owner role, which has no login permissions. 1.5 --- You can either: 1.6 --- 1) login as default user and switch the user 1.7 --- 2) decide to override this decision and give login permissions 1.8 --- 3) use your superuser of choice to manage the database (not recommended!) 1.9 -create role lightpit_dbo with password 'lpit_dbo_changeme'; 1.10 - 1.11 --- Create the actual (unprivileged) database user 1.12 -create user lightpit_user with password 'lpit_user_changeme'; 1.13 - 1.14 --- Create the LightPIT schema 1.15 -create schema lightpit authorization lightpit_dbo; 1.16 - 1.17 - 1.18 --- Grant basic privileges to user (the granting user must be the dbo) 1.19 -alter default privileges for role lightpit_dbo in schema lightpit 1.20 - grant select, insert, update, delete on tables to lightpit_user; 1.21 -alter default privileges for role lightpit_dbo in schema lightpit 1.22 - grant usage, select on sequences to lightpit_user; 1.23 -alter default privileges for role lightpit_dbo in schema lightpit 1.24 - grant execute on functions to lightpit_user; 1.25 -alter default privileges for role lightpit_dbo in schema lightpit 1.26 - grant usage on types to lightpit_user;
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 2.2 +++ b/setup/postgres/psql_create_database.sql Sun Nov 26 16:51:57 2017 +0100 2.3 @@ -0,0 +1,27 @@ 2.4 +-- Create a database owner role, which has no login permissions. 2.5 +-- You can either: 2.6 +-- 1) login as default user and switch the user 2.7 +-- 2) decide to override this decision and give login permissions 2.8 +-- 3) use your superuser of choice to manage the database (not recommended!) 2.9 +create role lightpit_dbo with password 'lpit_dbo_changeme'; 2.10 + 2.11 +-- Create the actual (unprivileged) database user 2.12 +create user lightpit_user with password 'lpit_user_changeme'; 2.13 + 2.14 +-- Create the LightPIT schema 2.15 +create schema lightpit authorization lightpit_dbo; 2.16 +grant usage on schema lightpit to lightpit_user; 2.17 + 2.18 +-- Grant basic privileges to user (the granting user must be the dbo) 2.19 +alter default privileges for role lightpit_dbo in schema lightpit 2.20 + grant select, insert, update, delete on tables to lightpit_user; 2.21 +alter default privileges for role lightpit_dbo in schema lightpit 2.22 + grant usage, select on sequences to lightpit_user; 2.23 +alter default privileges for role lightpit_dbo in schema lightpit 2.24 + grant execute on functions to lightpit_user; 2.25 +alter default privileges for role lightpit_dbo in schema lightpit 2.26 + grant usage on types to lightpit_user; 2.27 + 2.28 +-- restrict the search path to the lightpit schema 2.29 +alter role lightpit_dbo set search_path to lightpit; 2.30 +alter role lightpit_user set search_path to lightpit;