/* **********************************************************
 * Copyright 2020 VMware, Inc.  All rights reserved. -- VMware Confidential
 * **********************************************************/

/*==============================================================*/
/* vLCM_initialsetup_PostgreSQL.sql                             */
/* DBMS name:      PostgreSQL Version                           */
/*==============================================================*/

/* Create the db user and grant it ownership to the database */
CREATE USER :dbuser WITH PASSWORD :quoted_dbpass;

/* Create the schema */
CREATE SCHEMA :dbschema AUTHORIZATION :dbuser;

/* Set search path to schema for the db user */
ALTER USER :dbuser SET search_path = :dbschema;

/* Grant privileges to the db user */
GRANT CONNECT ON DATABASE :quoted_dbname TO :dbuser;
GRANT TEMP ON DATABASE :quoted_dbname TO :dbuser;
GRANT USAGE ON LANGUAGE plpgsql TO :dbuser;
GRANT ALL PRIVILEGES ON SCHEMA :dbschema TO :dbuser;
/* Grant replication monitoring permissions*/
GRANT pg_monitor TO :dbuser;


ALTER DEFAULT PRIVILEGES IN SCHEMA :dbschema GRANT ALL PRIVILEGES ON TABLES TO :dbuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA :dbschema GRANT ALL PRIVILEGES ON SEQUENCES TO :dbuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA :dbschema GRANT ALL PRIVILEGES ON FUNCTIONS TO :dbuser;
