Environment:

YugabyteDB-Anywhere - All versions

Issue:

This process can be followed to manually disable YSQLSH if it is enabled.

Resolution:

IMPORTANT INFORMATION:
Once YSQL is disabled, it cannot be enabled again. Please be cautious if you intend to use YSQL workload on this universe in future.

The eight steps below outline the procedure needing to be followed to disable the API. Reviewing the steps before executing on a system is highly recommended.

Step 1: We can edit the G-flag by going to Actions --> Edit Flags.

Step 2: Set `enable_ysql` as false on both T-server as well as Master nodes and do a rolling restart. This will disable it for the database node.

Once the `enable_ysql` is set to false, there will be health check failures.

In order to disable ysqlsh connectivity healthchecks for the YB-Anywhere, please follow below steps:

Step 1: SSH into the node which is hosting the replicated UI.

Step 2: Exec into the postgres container.

sudo docker exec -it postgres /bin/bash

Step 3: Sign into postgres

psql -U postgres -d yugaware

Step 4: Run below to check the current value for ysql flag:

select name, jsonb_pretty(universe_details_json::jsonb -> 'clusters' -> 1 -> 'userIntent') from universe where name = 'universeName';

 name | jsonb_pretty
----------------+---------------------------------------------------------
test-universe2 | { +
| "numNodes": 3, +
| "provider": "a3801161-0ea0-48a5-a320-a0d75ccdd8da",+
| "deviceInfo": { +
| "diskIops": 3000, +
| "numVolumes": 1, +
| "throughput": 125, +
| "volumeSize": 250, +
| "storageType": "GP3", +
| "storageClass": "standard" +
| }, +
| "enableIPV6": false, +
| "enableYCQL": true, +
| "enableYSQL": true, +
| "regionList": [ +
| "72e05856-4469-4301-a98c-31d690423b30" +
| ], +
| "useSystemd": false, +
| "enableYEDIS": false, +
| "useHostname": false, +
| "useTimeSync": true, +
| "instanceTags": { +
| }, +
| "instanceType": "c5.4xlarge", +
| "masterGFlags": { +
| "enable_ysql": "false" +
| }, +
| "providerType": "aws", +
| "universeName": "test-universe2", +
| "accessKeyCode": "aanandwala-new", +
| "tserverGFlags": { +
| "enable_ysql": "false" +
| }, +
| "assignPublicIP": true, +
| "enableYCQLAuth": false, +
| "enableYSQLAuth": false, +
| "replicationFactor": 3, +
| "ybSoftwareVersion": "2.13.2.0-b122", +
| "assignStaticPublicIP": false, +
| "enableExposingService": "UNEXPOSED", +
| "enableVolumeEncryption": false, +
| "enableNodeToNodeEncrypt": true, +
| "enableClientToNodeEncrypt": true +
| }

Step 5: Update the json to disable ysql:

BEGIN;
update universe set universe_details_json = jsonb_set(universe_details_json::jsonb, '{clusters, 1, userIntent, enableYSQL}', 'false') where name = 'universeName';

Step 6: Confirm the change i.e "enableYSQL": false 

select name, jsonb_pretty(universe_details_json::jsonb -> 'clusters' -> 1 -> 'userIntent') from universe where name = 'universeName';

 name | jsonb_pretty
----------------+---------------------------------------------------------
test-universe2 | { +
| "numNodes": 3, +
| "provider": "a3801161-0ea0-48a5-a320-a0d75ccdd8da",+
| "deviceInfo": { +
| "diskIops": 3000, +
| "numVolumes": 1, +
| "throughput": 125, +
| "volumeSize": 250, +
| "storageType": "GP3", +
| "storageClass": "standard" +
| }, +
| "enableIPV6": false, +
| "enableYCQL": true, +
| "enableYSQL": false, +
| "regionList": [ +
| "72e05856-4469-4301-a98c-31d690423b30" +
| ], +
| "useSystemd": false, +
| "enableYEDIS": false, +
| "useHostname": false, +
| "useTimeSync": true, +
| "instanceTags": { +
| }, +
| "instanceType": "c5.4xlarge", +
| "masterGFlags": { +
| "enable_ysql": "false" +
| }, +
| "providerType": "aws", +
| "universeName": "test-universe2", +
| "accessKeyCode": "aanandwala-new", +
| "tserverGFlags": { +
| "enable_ysql": "false" +
| }, +
| "assignPublicIP": true, +
| "enableYCQLAuth": false, +
| "enableYSQLAuth": false, +
| "replicationFactor": 3, +
| "ybSoftwareVersion": "2.13.2.0-b122", +
| "assignStaticPublicIP": false, +
| "enableExposingService": "UNEXPOSED", +
| "enableVolumeEncryption": false, +
| "enableNodeToNodeEncrypt": true, +
| "enableClientToNodeEncrypt": true +

Step 7: Commit the change:

commit;

Step 8: Refresh UI and see if it shows YSQL disabled for the universe.