What to expect
Windows service
The CAST Storage Service is installed as a Windows Service (click to enlarge):
It is configured as follows:
- Startup Type: set to Automatic to restart the service should it stop or if Windows is rebooted.
- Log On As: Local System
Windows processes
When the CAST Storage Service is running, several PostgreSQL related processes will be seen in Task Manager (click to enlarge):
Windows Start Menu
The installer will create the following Start menu items automatically. pgAdmin3/pgAdmin4 launches the PostgreSQL database management console which is provided with CAST Storage Service:
CSS 4 | |
---|---|
CSS 3 | |
CSS 2 |
Default database users/roles and passwords
When a CAST Storage Service is installed, two additional users/roles are created (operator/guest) by default as listed below. An additional user (postgres) is present out of the box:
User | Default password | Purpose |
---|---|---|
operator | CastAIP | This user/role is designed to be used for all interaction with CAST AIP applications. It has full read and write access to all the schemas stored on the CAST Storage Service with the following privileges: See Changing the default operator and guest passwords for CAST Storage Service if you would like to change the default password. |
guest | WelcomeToAIP | This user/role is designed to be used by third party tools requiring simple read-access to the schemas stored on the CAST Storage Service with the following privileges:
|
postgres | - | The postgres user/role is created by default with the privileges as listed in the images below: If you are concerned about security, you should disable logins for this user/role, and/or change the default password: Disable login Using PgAdmin (which is bundled with CAST Storage Service):
Change the password Using PgAdmin (which is bundled with CAST Storage Service):
Or use a SQL query: ALTER USER postgres WITH PASSWORD 'password'; |
Third-party items are installed with CAST Storage Service
When you complete an installation using the CAST Storage Service installer, if they are not already installed, the following third-party items will be installed to the target workstation:
CSS 4 |
|
---|---|
CSS 3 |
|
CSS 2 |
|
postgres database
The "postgres" database is created by default in PostgreSQL. CAST products will, by default, expect to create/connect to schemas in this database. However, it is also possible to configure CAST products to connect to a custom database other than "postgres". See Using a custom CAST Storage Service or PostgreSQL database other than the default postgres.
Action items
Edit the pg_hba.conf file to allow/deny incoming connections
By default, CAST Storage Service is configured to allow incoming connections on IPv4 and IPv6 from anywhere via the pg_hba.conf file (see https://www.postgresql.org/docs/current/auth-pg-hba-conf.html for more information). This may not be desirable in your environment. If not, you can edit the following file with a text editor:
%PROGRAMFILES%\CAST\CastStorageService<version>\db_data\pg_hba.conf
Locate the following section and modify as required:
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust # This line allows any client to connect to the server. Doing so, we don't need to manage it anymore. #host all all 0.0.0.0/0 scram-sha-256 host all all 0.0.0.0/0 md5 # IPv6 local connections: host all all ::1/128 md5 host all all ::1/0 md5 #host all all ::1/128 scram-sha-256 # Allow replication connections from localhost, by a user with the # replication privilege. #local replication all scram-sha-256 #host replication all 127.0.0.1/32 scram-sha-256 #host replication all ::1/128 scram-sha-256
The following lines allow connections from anywhere:
host all all 0.0.0.0/0 md5 (IPv4) host all all ::1/128 md5 (IPv6) host all all ::1/0 md5 (IPv6)
For example, you could allow only a specific subnet on IPv4:
host all all 172.20.143.0/24 md5
You can also restrict access to specific databases and logins. For example, to allow only the operator login to access the postgres database from any client, add the following like:
host postgres operator 0.0.0.0/0 md5
When you have made any changes, save the file and restart the CAST Storage Service so that the changes are taken into account.
Edit the postgresql.conf file to tune the server
To check if the server itself is configured correctly for CAST AIP, we need to verify and modify certain parameters in the postgresql.conf file, located here:
%PROGRAMFILES%\CAST\CastStorageService<version>\db_data\postgresql.conf
Tune RAM allocation
The settings in postgresql.conf are directly related to available RAM for the PostgreSQL instance - and as such their configuration is specific to the host machine. Please use http://pgtune.leopard.in.ua/ on the host machine to identify the correct settings and then modify postgresql.conf with the settings provided by pgtune. You should update the following settings with the values provided by pgtune:
- effective_cache_size
- min_wal_size
- max_wal_size
- wal_buffers
When you have made any changes, save the file and restart the CAST Storage Service so that the changes are taken into account.
When running pgtune, choose the following:
- Set the DB version to the installed version
- Set the DB Type to Mixed type of applications
- Set the Number of connections to 300
Some of the values suggested by pgtune are somewhat low. Therefore, please calculate the following postgresql.conf settings as follows:
- shared_buffers - value should be 25% of available RAM with a max of 8 GB
maintenance_work_mem= 512 MB
Other settings
The following settings should also be modified to the values listed below. When you have made any changes, save the file and restart the CAST Storage Service so that the changes are taken into account.
Parameters | Required value | Comments |
---|---|---|
listen_addresses | '*' | Instead of localhost by default. Enables connectivity from other machines. |
Port | 2282 or 2284 | |
max_connections | 300 | |
fsync | off | |
synchronous_commit | off | |
full_page_writes | off | |
commit_delay | 10 | |
checkpoint_completion_target | 0.9 | |
cursor_tuple_fraction | 1.0 | |
log_checkpoints | on | |
log_destination | 'stderr' | |
logging_collector | on | |
log_line_prefix | '%t [%p]: [%l-1] ' | Don't forget the space before final quote mark. |
log_temp_files | 1024kB | |
log_autovacuum_min_duration | 1000ms | |
autovacuum_vacuum_cost_limit | 200 | |
bytea_output | 'escape' | |
datestyle | 'iso, mdy' | Ensure that this option is active (it may already be active). |
lc_messages | 'C' | |
lc_monetary | 'C' | |
lc_numeric | 'C' | |
lc_time | 'C' | |
max_locks_per_transaction | 4096 | Higher value than 64 by default. |
standard_conforming_strings | on |
Create custom database users - optional
If you are using CAST AIP ≥ 8.3.12, you are also free to create your own users and then use them with CAST AIP and related software - you need to have a minimum of one user with the SUPERUSER permission and then grant the "postgres" role to this user. You can create custom users using a tool such as PGAdmin. An example SQL script for a user called "my_custom_user" is shown below:
CREATE USER my_custom_user WITH LOGIN SUPERUSER CREATEDB CREATEROLE INHERIT REPLICATION CONNECTION LIMIT -1 PASSWORD 'xxxxxx'; GRANT postgres TO my_custom_user;
When using CAST AIP ≥ 8.3.30, it is possible to create custom users that do NOT require the SUPERUSER permission if you prefer not to grant this. For example, this script creates a role "my_custom_role" with the password "my_password" that can login. The role has not been granted the SUPERUSER permission and instead only the minimum permissions required to operate CAST AIP are granted:
CREATE ROLE my_custom_role LOGIN PASSWORD 'my_password' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; GRANT CREATE, CONNECT, TEMPORARY on DATABASE postgres to my_custom_role;