Deploying SAS Viya 2024.06 (or later) with an external Postgres Database- what you should know
If you are about to deploy the latest SAS Viya version (2024.06 or later) and are using an external PostgreSQL server, there is a new system requirement.
If you miss it, your deployment may fail….This post explains the rationale and provides some guidance on how to meet this new requirement. We'll look at the "What?", "Why?" and "How?".
What?
From the "What’s new page" for SAS Viya stable 2024.06:
“Starting with 2024.06, the SharedServices database for an external PostgreSQL server is no longer created automatically during the initial deployment of the SAS Viya platform. Instead, you must manually create it before you start the SAS Viya platform deployment. This database supports the required SAS Infrastructure Data Server component."
In a SAS Viya deployment, the SAS Data Server Operator pod (aka "DSO") is the component responsible for creating the SharedServices database in the SAS Infrastructure Data Server (aka "IDS") instance.
From the stable 2024.06 version, the DSO does not create the SharedServices database if the PostgreSQL implementation is external. The DSO still creates the SharedServices database if the Internal Crunchy Data Server is used.
Symptoms if you miss it
It is an important change.
If we forget about this requirement and deploy as we did prior to Stable 2024.06, we would probably see that our deployment fails with most pods either in "error" or stuck in the "Init: 0/x " status with a message like the one below from the sas-start-sequencer container’s log:
{"level":"info","version":1,"source":"start-sequencer","messageKey":"DB Check
sleeping","messageParameters":{"elapsed time":"21m:29s:301ms","name":"main"},"properties":
{"caller":"sdsci/main.go:405
In k9s, the pod’s view would look like that:
And after some troubleshooting, we would ultimately identify the root cause by looking in the SAS Data Server Operator pod’s log (the pod’s name is something like "sas-data-server-operator-c97c5c796-nxmnc") :
{"level":"error","version":1,"source":"sas-data-server-operator-c97c5c796-nxmnc",
"messageParameters": {"p1":"failed to connect to `host=frarpo-r-0294-default-
flexpsql.postgres.database.azure.com user=pgadmin database=SharedServices`: server error
(FATAL: database \"SharedServices\" does not exist (SQLSTATE 3D000))"}, "messageKey":"failed
to initialize database, got error%v", "properties":{"logger":"commons/storage/gorm", "file":"/go/pkg/mod/gopkgs.sas.com/sonder/v2@v2.42.2/storage/gorm/gorm.go:406", "caller":"gorm/gorm.go:607"},"timeStamp":"2024-06-26T09:43:46.992640+00:00","message":"failed to initialize database, got error %v"}
…
{"level":"error","version":1,"source":"sas-data-server-operator-c97c5c796-nxmnc","messageKey": "Reconciler error", "properties":{"error":"database server is external and cannot connect to the SAS database", "caller":"logr/logr.go:49"}, "attributes":{"controllerKind":"DataServer","DataServer":{"name":"sas-platform-postgres","namespace":"test"},"namespace":"test","name":"sas-platform-postgres", "reconcileID":"f7de99f8-f6b0-4a96-9e6f-53a9a8570ab5", "controller":"dataserver","controllerGroup":"webinfdsvr.sas.com"}, "timeStamp":"2024-06-26T09:43:46. 992706+00:00","message":"Reconciler error"}An important detail…
This change, in the way the DSO handles an external PostgreSQL, relies on the presence of a specific annotation on the DataServer Custom Resource Definition which is only added if the new version of the PatchTransformer template delivered from version 2024.06 is applied.
When the DSO detects this annotation, it does NOT create the SharedServices Database in case it is not there. Instead, it only logs the fact that the SharedServices database is not there, with an "error" message (as shown above).
Here is an extract of the new PatchTransformer file with Stable 2024.06, usually called platform-postgres-dataserver-transformer.yaml or cds-postgres-dataserver-transformer.yaml (per the README instructions).
So if this sas.com/database-server-location annotation is NOT set for the DataServer Custom Resource, then there is no change and the SharedServices database is created by the DSO, in the external PostgreSQL server, just as before.
Here are some examples of cases in which the new annotation is not added:
- As of today, if you deploy SAS Viya with the DaC tool - viya4-deployment GitHub project which has its own copy of the xxx-dataserver-transformer.yaml (without the annotation).
- or if you reference a static version of this configuration file in your Kustomization.yaml file from a previous SAS Viya version in your deployment of 2024.06.
In either of these situations, you won't see any change of behavior – the SharedServices database is automatically created, as before, by the DSO, even for an external PostgreSQL server.
Why?
The reasons leading to this DSO change in 2024.06 are related to security concerns :
Here is the specific requirement that could be problematic for security conscious database administrators:
The user that is the database owner must also have CONNECT privileges on the system database named postgres to enable SAS Viya platform services to start up.
The long-term goal is to remove the need to have such privileges associated to the PostgreSQL account used in the SAS Viya configuration.
The 2024.06 change removes the need for the Data Server Operator (DSO) to require this privilege (If a customer uses external Postgres and correctly pre-creates the SharedServices database, then the DSO won't connect to the "postgres" system database in the first place).
However…if you look at the external PostgreSQL requirement, you can see that we still have this requirement ☹:
It is because, outside of Data Server Operator (DSO), the Java services of the Viya platform still require a user with the CONNECT privileges to the system database (named postgres). Work is in progress to move to a newer Java framework and once all Java services will be using it, then there would be no need for a PostgreSQL account with to the system database for an external DB server.
With 2024.06, we are taking a first step toward the goal to reduce the permissions requirements on the Postgres user account used by Viya to interact with the external PostgreSQL server.
How?
Now…How can we avoid our deployment with the external postgres failing when we deploy stable 2024.06 ?
Pre-create the SharedServices Database
From the 2024.06 version of SAS Viya, if you are using external Postgres then you need to create the SharedServices database before starting the deployment process. Some guidelines are provided in the official documentation for this new requirement:
But while there is a link to the official PostgreSQL documentation, there are no command examples.
To create the SharedServices database, you should be able to run the code below with the user that you were passing into DSO.
CREATE DATABASE "SharedServices" WITH TEMPLATE template0 LC_COLLATE 'C' LC_CTYPE 'C';
REVOKE ALL on DATABASE "SharedServices" FROM public;
This is the SQL command that the DSO was running until 2024.06 (and keeps running when the PostgreSQL instance is internal).
That seems pretty easy, right?
Yes it is…As long as you are connected to the PostgreSQL database with an account with enough privileges to do so.
In customers environments, the database administrator should be able to connect to the external PostgreSQL server and run this command with the appropriate account.
Otherwise, here is a little trick to do it with a few commands.
Pro-tip: Create the Shared Service from an ad-hoc pod
The idea is to start an ad-hoc temporary pod, in the Kubernetes cluster, to run a psql command to create the Database.
In the example below, we set our information in environment variable and then we instantiate a pod in which we can run the command to create the database.
# Set required postgres variables
PG_ADMIN_USER=pgadmin
POSTGRES_PASSWORD="<my-super-password>"
PG_SERVER=frarpo-r-0294-default-flexpsql.postgres.database.azure.com
PG_PORT=5432
NS="test"
# Create the SharedServices database
kubectl run postgresql-dev-client --rm --tty -i --restart='Never' --namespace $NS --image docker.io/bitnami/postgresql:13 \
--env="PGPASSWORD=$POSTGRES_PASSWORD" \
--command -- psql -U $PG_ADMIN_USER --host $PG_SERVER -d postgres -p $PG_PORT
This command opens a psql prompt:
Then you can type the SQL commands noted above to create and secure the SharedServices database:
The SharedServices database should be created before deploying SAS Viya.
However, if this requirement has been forgotten, it is still possible to do it even after the SAS Viya deployment has failed (due to the missing SharedServices database). But you must delete the DSO pod (sas-data-server-operator), after you run the SQL command. When a new DSO pod is started, it detects the SharedServices database and, thanks to the Kubernetes auto-healing capabilities, all of the SAS Viya pods should recover to a running/ready state (this scenario was tested in our GEL lab).
Specific case: If you use the IaC tool for Upstream opensource Kubernetes
Finally, there is another particular case where you would NOT be impacted by this change.
If you are deploying SAS Viya in upstream open-source Kubernetes and if you decide to use the "SAS Viya 4 Infrastructure as Code (IaC) for Open Source Kubernetes" to automatically install the PostgreSQL server, there is already an ansible task that creates the SharedServices database and grant all the privileges to the configured PostgreSQL administrator account.
In this case, the DSO change with 2024.06 will be transparent for you.
Conclusion
There is always a tradeoff to make between simplicity and security.
This change adds a requirement, with an additional task (pre-create the SharedServices database), either for the customer’s database administrator or for the person in charge of the Viya deployment. Despite the additional work, the expectation is that the reliance on a PostgreSQL super user account (with permissions on the postgres system database) can be completely removed in later SAS Viya versions.
That would allow SAS Viya to support the Zero Trust principle of "least privileges".
Find more articles from SAS Global Enablement and Learning here.
