...

When creating the database then consider to use using a UTF based character encoding and collating sequence, for example:

...

# generate SQL statement to grant permissions to run-time account
SELECT DISTINCT
       CASE
            WHEN object_type = 'SEQUENCE' THEN 'GRANT SELECT'
            WHEN object_type IN ('TABLE', 'VIEW') THEN 'GRANT SELECT,INSERT,UPDATE,DELETE'
       END
       || ' ON JS7_OWNER.'|| object_name || ' TO JS7_USER;' 
  FROM dba_objects 
 WHERE object_type IN ('TABLE','VIEW','SEQUENCE') and owner='JS7_OWNER';

Setup for PostgreSQL®

Database

Accounts

...

Database

When creating the database then consider using a UTF based character encoding and collating sequence, for example:

# set up database
CREATE DATABASE JS7
ENCODING 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8';

Accounts

The user is created in Postgres with a Password and is used as an account to login to the Postgres database, for example:

# add an account
create user JS7_USER with password 'JS7_USER';

Permissions

The following permissions are required for the schema:

• to manage objects
  • Tables, Views, Functions, Stored Procedures: CREATE, DROP, ALTER
• to access objects at run-time
  • Tables, Views: SELECT, INSERT, UPDATE, DELETE
  • Functions, Stored Procedures: EXECUTE

Postgres provide a Role that has the permissions and in reference can be assigned to the user. The commands to create a role and assign it to the user are like this:

# create a role
create role JS7_ROLE WITH LOGIN;

# grant permissions as specified above to the role
grant USAGE on schema public to JS7_ROLE;
 
grant SELECT,UPDATE,INSERT,DELETE on all tables in schema public to JS7_ROLE;
grant EXECUTE on all functions in schema public to JS7_ROLE;
grant CREATE on schema public to JS7_ROLE;

# assign the role to the user
 grant JS7_ROLE to JS7_USER;

Connection Pool

JS7 makes use of a connection pool to allow a larger number of JOC Cockpit user sessions to share database connections from the pool.

...