Configure the database schemas

CREATE USER <repository_user> IDENTIFIED BY <repository_password>
 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, RESOURCE TO <repository_user>;

-- Use the following command for Oracle 12c and above
GRANT UNLIMITED TABLESPACE TO <repository_user>;

-- Create a database for the repository and data locations
CREATE DATABASE <postgresql_database_name> WITH ENCODING 'UTF8';

-- Disconnect and then reconnect using one of the following options:
-- the JDBC URL: jdbc:postgresql://<host>:<port>/<postgresql_database_name>
-- psql with the following command: psql -U postgres <postgresql_database_name>

CREATE SCHEMA extensions;
GRANT USAGE ON SCHEMA extensions TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA extensions GRANT EXECUTE ON FUNCTIONS TO PUBLIC;
ALTER DATABASE <postgresql_database_name> SET SEARCH_PATH TO "$user",public,extensions;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp"     with schema extensions;
CREATE EXTENSION IF NOT EXISTS "fuzzystrmatch" with schema extensions;

-- Create the repository user and schema
CREATE USER <repository_user> WITH PASSWORD '<repository_password>';

-- Use the following syntax for PostgreSQL 9
-- CREATE USER <repository_user> WITH UNENCRYPTED PASSWORD '<repository_password>';

-- The following command is required only for PostgreSQL running on Amazon RDS.
-- It grants access to the repository to the RDS superuser.
-- GRANT <repository_user> TO <rds_superuser_name>

CREATE SCHEMA <repository_user> AUTHORIZATION <repository_user>;

-- Create a database for the repository
CREATE DATABASE <repository_database_name>
GO

-- Configure the database
ALTER DATABASE <repository_database_name> SET READ_COMMITTED_SNAPSHOT ON;
GO

ALTER DATABASE <repository_database_name> SET QUOTED_IDENTIFIER ON;
GO

-- Create a login to connect the database
CREATE LOGIN <repository_user> WITH PASSWORD='<repository_password>', DEFAULT_DATABASE=<repository_database_name>
GO

-- Add a user for that login in the database
USE <repository_database_name>
GO

CREATE USER <repository_user> FOR LOGIN <repository_user>
GO

-- Make this user database owner
ALTER ROLE db_owner ADD MEMBER <repository_user>
GO

-- Create the repository read-only user
CREATE USER <repository_readonly_user>
 IDENTIFIED BY <repository_readonly_password>
 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

-- Grant minimum access to the user
GRANT CONNECT TO <repository_readonly_user>;

-- Run the following commands after the repository creation.
-- Grant select privileges on the profiling tables
GRANT SELECT ON <repository_user>.PRF_PROFILING TO <repository_readonly_user>;
GRANT SELECT ON <repository_user>.PRF_TABLE TO <repository_readonly_user>;
GRANT SELECT ON <repository_user>.PRF_COLUMN TO <repository_readonly_user>;
GRANT SELECT ON <repository_user>.PRF_DIST_VALS TO <repository_readonly_user>;
GRANT SELECT ON <repository_user>.PRF_DIST_PATTERNS TO <repository_readonly_user>;

-- The <repository_readonly_user> user must use the following statement to default their schema to the repository.
-- ALTER SESSION SET CURRENT_SCHEMA = <repository_user>;

-- Create the repository read-only user
CREATE USER <repository_readonly_user> WITH PASSWORD '<repository_readonly_password>';

-- Use the following syntax for PostgreSQL 9
-- CREATE USER <repository_readonly_user> WITH UNENCRYPTED PASSWORD '<repository_readonly_password>';

GRANT CONNECT ON DATABASE <postgresql_database_name> to <repository_readonly_user>;

-- Set the search path to include the repository
ALTER ROLE <repository_readonly_user> SET SEARCH_PATH TO "$user", <repository_user>,public,extensions;

-- Run the following commands after the repository creation
-- Grant select privileges on the profiling tables
GRANT USAGE ON SCHEMA <repository_user> TO <repository_readonly_user>;
GRANT SELECT ON TABLE
    <repository_user>.PRF_PROFILING,
    <repository_user>.PRF_TABLE,
    <repository_user>.PRF_COLUMN,
    <repository_user>.PRF_DIST_VALS,
    <repository_user>.PRF_DIST_PATTERNS
TO <repository_readonly_user>;

-- Create the repository read-only user
CREATE LOGIN <repository_readonly_user> WITH PASSWORD='<repository_readonly_password>', DEFAULT_DATABASE=<repository_database_name>
GO

-- Add a user for that login in the database
USE <repository_database_name>
GO

CREATE USER <repository_readonly_user> FOR LOGIN <repository_readonly_user>;
GO

-- Grant minimum access to the user
GRANT CONNECT TO <repository_readonly_user>;

-- Run the following commands after the repository creation
-- Grant select privileges on the profiling tables
GRANT SELECT ON PRF_PROFILING TO <repository_readonly_user>;
GRANT SELECT ON PRF_TABLE TO <repository_readonly_user>;
GRANT SELECT ON PRF_COLUMN TO <repository_readonly_user>;
GRANT SELECT ON PRF_DIST_VALS TO <repository_readonly_user>;
GRANT SELECT ON PRF_DIST_PATTERNS TO <repository_readonly_user>;