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