Deploying SAS Viya with an External PostgreSQL

4 Likes

The SAS Infrastructure Data Server stores SAS Viya user content, such as reports, authorization rules, selected source definitions, attachments, and user preferences. With Viya 3 it was necessarily an internal component of the platform; starting with SAS Viya 2020, this component can also be replaced by an external service. The server requirements and configuration settings required to use an external instance have changed over time; in this article, we describe the most current ones as of release Stable 2023.04.

Two Available Options

The SAS Infrastructure Data Server requires an instance of PostgreSQL. By default, the deployment installs and configures its own

opensource PostgreSQL database inside the SAS Viya Kubernetes cluster; this is referred as an internal instance. SAS deploys a comprehensive PostgreSQL container operator suite provided by Crunchy Data for this purpose. If you want, you can choose instead to provide another PostgreSQL instance, which is called an external instance.

The choice to use an internal or external instance should be made before deploying the software. Once you have decided to use the external PostgreSQL database, there is no turning back: after the deployment has been completed with the external PostgreSQL, it is not possible to change to use an internal PostgreSQL (it is also true in the other way around). If you change your mind, you will have to unconfigure and re-deploy from zero.

External PostgreSQL Requirements

SAS Viya supports managed PostgreSQL instances hosted by a cloud provider, or directly configured and maintained by you:

PostgreSQL (Open Source)
Microsoft Azure Database for PostgreSQL - Single Server (PostgreSQL 11 only)
Microsoft Azure Database for PostgreSQL - Flexible Server
Amazon RDS for PostgreSQL
GCP: Cloud SQL for PostgreSQL

PostgreSQL versions 11 - 14 are the currently supported versions. SAS Viya platform 2022.10 or later is required if you want to use PostgreSQL 13, while SAS Viya platform 2023.03 or later is required for PostgreSQL 14.

The PostgreSQL server should be set up and running before starting the SAS deployment. The SAS Viya Operations document, in the System Requirements section, lists all the requirements such as role permissions and configuration settings. The most important one to consider for proper instance sizing is supporting a maximum number of connections and max_prepared_transactions of at least 1024. Please refer to the links in the appendix to find the proper PostgreSQL instance types that can satisfy that requirement for each supported cloud vendor.

How to Configure SAS Viya to Use an External Instance of PostgreSQL

Once the external PostgreSQL server has been identified, configured, and confirmed to be running, it’s time to deploy SAS Viya with the proper settings to use it. Step-by-step instructions are provided in the README file at $deploy/sas-bases/examples/postgres/configure/README.md (for Markdown format) or at $deploy/sas-bases/docs/configure_postgresql.htm (for HTML). Starting with the release Stable 2022.10 there have been breaking changes from previous instructions, and older configuration files cannot be reused. The good news is that the actual process has not changed much: find the connection details, put them in the proper configuration files, and reference those files in SAS Viya base kustomization.yaml.

Find the Connection Details

To be able to connect to an external PostgreSQL you will need the following parameters from the database administrator:

Database role (i.e. username) and password
Database name (optional)
Server host name and port

The database name parameter should point to the database inside the PostgreSQL server SAS Viya will use; if omitted, it defaults to "SharedServices".

SAS recommends securing the connection between SAS Viya and the external database using TLS encryption. If the certificate used is provided by a well-known Certificate Authority, then SAS Viya will automatically recognize and validate it. Instead, if the certificate is self-signed, your database administrator or cloud provider will have to provide the server CA certificate, which you will then include in the SAS Viya trust store following the procedure outlined in the SAS Viya: Security guide.

Create the Configuration Files

Template configuration files are provided under $deploy/sas-bases/examples/postgres/. Simply copy the postgres-user.env and dataserver-transformer.yaml files to $deploy/site-config/postgres/ and change the placeholders with the actual values collected in the previous step, as guided by the comments in the files.

# Replace {{ DB-ROLE }} with the name of the database role SAS Viya will use.
username={{ DB-ROLE }}
# Replace {{ DB-ROLE-PASSWORD }} with the password of the database role SAS Viya will use.
password={{ DB-ROLE-PASSWORD }}

postgres-user.env

# Example dataserver-transformer to modify DataServer CustomResources.
#   - In the following code, user-defined values are indicated by a capitalized
#     and hyphenated name set off by curly braces and a space at each end. To
#     replace the variable, replace the curly braces, interior spaces, and the
#     variable name. For instance, {{ DB-NAME }} could be replaced by
#     SharedServices.
#   - Replace all instance of {{ DATASERVER-NAME }} with the name of the
#     DataServer CustomResource you are targeting. Refer to the below list to
#     find the correct name based on the PostgreSQL server you are modifying.
#       - For Platform PostgreSQL, use "sas-platform-postgres"
#       - For CDS PostgreSQL, use "sas-cds-postgres"
apiVersion: builtin
kind: PatchTransformer
metadata:
  name: {{ DATASERVER-NAME }}-dataserver-transformer
patch: |-
  # # (Optional) Uncomment this section to change which database inside the
  # # PostgreSQL server Viya will use by default. Defaults to "SharedServices"
  # # if not specified. Replace {{ DB-NAME }} with the name of the database.
  # - op: replace
  #   path: /spec/databases/0/name
  #   value: {{ DB-NAME }}

  # Replace {{ POSTGRES-USER-SECRET-NAME }} with the name of the
  # secretGenerator containing PostgreSQL user credentials.
  - op: replace
    path: /spec/users/0/credentials/input
    value:
      secretRef:
        name: {{ POSTGRES-USER-SECRET-NAME }}
      usernameKey: username # For internal use, do not modify
      passwordKey: password # For internal use, do not modify

  # Replace {{ DB-HOST }} with the host name for the PostgreSQL server.
  - op: replace
    path: /spec/registrations/0/host
    value: {{ DB-HOST }}

  # Replace {{ DB-PORT }} with the port number for the PostgreSQL server.
  - op: replace
    path: /spec/registrations/0/port
    value: {{ DB-PORT }}

  - op: replace
    path: /spec/ssl
    value: true
target:
  group: webinfdsvr.sas.com
  kind: DataServer
  version: v1beta1
  name: {{ DATASERVER-NAME }}

dataserver-transformer.yaml

Edit kustomization.yaml

You add PostgreSQL servers to SAS Viya via the DataServer webinfdsvr.sas.com CustomResource. This CustomResource can be configured to reference an external PostgreSQL cluster. In that case, the CustomResource also references a Kubernetes Secret that contains username and password used for the connection. This is accomplished by adding the two files above to kustomization.yaml:

include the dataserver-transformer.yaml file in the transformers block
reference the postgres-user.env in the secretGenerator block

Note: be sure to use the same name, for the secretGenerator in kustomization.yaml, as the value you used for the {{ POSTGRES-USER-SECRET-NAME }} placeholder in dataserver-transformer.yaml

transformers:
- site-config/postgres/dataserver-transformer.yaml
...
secretGenerator:
- name: platform-postgres-user
  envs:
  - site-config/postgres/postgres-user.env

Deploying with SAS Viya 4 IaC tools

Instead of the manual configuration process outlined so far, it is possible to use the SAS Viya 4 Infrastructure as Code GitHub tools, and have them create and use an external database for PostgreSQL.

Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.

You have 2 different choices:

Use the viya4-iac-azure, viya4-iac-aws, viya4-iac-gcp, viya4-iac-k8s projects to create a new instance of PostgreSQL. The tools abstract the platform-specific commands and provide many options that can be tweaked to tune the database server.
> Note: SAS does not provide any tools to automatically create infrastructure artifacts for Red Hat OpenShift. If OpenShift is your target platform, you can use third-party Operators to create a PostgreSQL instance, or reference a database server running in a different environment.
Reuse an existing database server instance.

The next step is then to use the SAS Viya 4 Deployment GitHub tool to deploy SAS Viya with the external PostgreSQL; depending on which of the two previous choices you made, you now have two different paths:

Integrate with the SAS Viya IaC tool. One of the final steps of IaC tools is to output a “state file” that contains all details of the artifacts they created, including PostgreSQL. The SAS Viya 4 Deployment tool can read this state file in input and automatically configure SAS Viya.
Integrate with the custom server instance. In this case, you have to provide, in the form of Ansible variables, the same connections details that we highlighted previously:

V4_CFG_POSTGRES_SERVERS:
  default:
    internal: false
    admin: 
    password: ""
    fqdn: 
    server_port: 5432
    ssl_enforcement_enabled: true
    database: SharedServices

Conclusion

SAS Viya provides many integration points to leverage external components and services. In this post, we have seen how to create an external PostgreSQL database server and configure SAS Viya Stable 2022.10 and later to use it.

Appendix: Links

Find more articles from SAS Global Enablement and Learning here.