Create Database Users and Storage for PostgreSQL
You cannot create storage creation for PostgreSQL using Database Component Configurator. To create the database user and storage for PostgreSQL, use the PgAdmin tool or PSQL prompt.
1. Create the database and schema:
CREATE DATABASE WM_PGDB;
CREATE SCHEMA WM_PGDB_schema;
2. Set the search path for the schema:
SET search_path TO WM_PGDB_schema;
3. Create the database user and set the password:
CREATE USER WM_PGDB_user;
ALTER USER WM_PGDB_user WITH ENCRYPTED PASSWORD 'xyz';
4. Set up the database user's default schema using the search path for the user:
ALTER USER WM_PGDB_user SET search_path = WM_PGDB_schema;
5. Grant permissions to the database user to connect to the database and to use and create objects in the schema:
GRANT CONNECT ON DATABASE WM_PGDB TO WM_PGDB_user;
GRANT USAGE, CREATE ON SCHEMA WM_PGDB_schema TO WM_PGDB_user;
GRANT ALL ON SCHEMA WM_PGDB_schema TO WM_PGDB_user;
6. Grant permissions in case the database user needs to create objects in the future:
ALTER DEFAULT PRIVILEGES IN SCHEMA WM_PGDB_schema GRANT ALL ON TABLES TO WM_PGDB_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA WM_PGDB_schema GRANT ALL ON SEQUENCES TO WM_PGDB_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA WM_PGDB_schema GRANT ALL ON FUNCTIONS TO WM_PGDB_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA WM_PGDB_schema GRANT ALL ON TYPES TO WM_PGDB_user;