While in terminal, log in to the psql console as postgres user:
sudo su -l postgres
Create users 'sos_admin' and 'sos_app' with passwords:
Create a admin user called tethys_admin with new password:
postgres=# create user sos_admin with encrypted password 'xxx';
CREATE ROLE
Create an app user called tethys_app with new password:
postgres=# create user sos_app with encrypted password 'xxx';
CREATE ROLE
Create db 'sosdb' and schema 'gba':
create a database called tethys via psql:
postgres=# create database sosdb;
CREATE DATABASE
user postgres ist default owner of db tethys
exit psql:
\q
and re-login into sosdb:
postgres@geomon:~$psql -d sosdb -U postgres -p 5432
create a schemma 'gba' for all tethys tabels with full authorization for the user 'tethys_admin':
sosdb=# CREATE SCHEMA IF NOT EXISTS gba AUTHORIZATION sos_admin;
CREATE SCHEMA
Change user privileges:
-> grant select, insert, update and delete privileges for the user 'sos_app':
sosdb=# grant usage on schema gba to sos_app;
GRANT
sosdb=# grant select, insert, update, delete on all tables in schema gba to sos_app;
GRANT
Default "select, insert, update, delete privileges" on tables to sos_app
sosdb=# alter default privileges for role sos_admin in schema gba grant select, insert, update, delete on tables to sos_app;
ALTER DEFAULT PRIVILEGES
Default usage privileges on sequences to sos_app
grant usage on all sequences in schema gba to sos_app:
sosdb=# alter default privileges for role sos_admin in schema gba grant usage on sequences to sos_app;
ALTER DEFAULT PRIVILEGES
Default execute privileges on function to sos_app
grant execute on all functions in schema gba to sos_app:
alter default privileges for role sos_admin in schema gba grant execute on functions to sos_app;
ALTER DEFAULT PRIVILEGES
Create extensions:
sosdb=# CREATE EXTENSION postgis;
CREATE EXTENSION
sosdb=# CREATE EXTENSION adminpack;
CREATE EXTENSION
sosdb=# CREATE EXTENSION pgcrypto;
CREATE EXTENSION
sosdb=# SELECT postgis_full_version();
exit to administrator user:
\q