Page History
...
When creating the database then consider to use using a UTF based character encoding and collating sequence, for example:
...
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
# 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®
Status colour Yellow title TODO
Database
Accounts
...
Database
When creating the database then consider using a UTF based character encoding and collating sequence, for example:
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
# 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:
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
# 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
- Tables, Views, Functions, Stored Procedures:
- to access objects at run-time
- Tables, Views:
SELECT, INSERT, UPDATE, DELETE
- Functions, Stored Procedures:
EXECUTE
- Tables, Views:
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:
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
# 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.
...