[release]: sign and release ODBC driver v.1.5.0.0 for Mac and Windows

[Yury-Fridlyand]

Did you read the on-boarding document

What is the name of your component?

OpenSearch SQL ODBC driver

What is the link to your GitHub repo?

https://github.com/opensearch-project/sql-odbc

Targeted release date

N/A

Where should we publish this component?

Artifacts and download page
https://opensearch.org/downloads.html#drivers

What type of artifact(s) will be generated for this component?

Mac installer: pkg
Win 32bit installer: msi
Win 64bit installer: msi

Have you completed the required reviews including security reviews, UX reviews?

Have you on-boarded automated security scanning for the GitHub repo associated with this component?

Additional context

ODBC was released only once, manually, on the very beginning of OpenSearch Project history.
Probably, release automation process should be created from the scratch.

A tag was cut for this release: 1.5.0.0
Release notes: https://github.com/opensearch-project/sql-odbc/blob/1.5.0.0/release-notes/sql-odbc.OpenSearch.release-notes-1.5.0.0.md
Release artifacts (installers) are generated by GHA CI: https://github.com/opensearch-project/sql-odbc/actions/runs/5271577922:

• OpenSearch SQL ODBC Driver 64-bit-1.5.0.0-Darwin.pkg
• OpenSearch SQL ODBC Driver 64-bit-1.5.0.0-Windows.msi
• OpenSearch SQL ODBC Driver 32-bit-1.5.0.0-Windows.msi

[Yury-Fridlyand]

Uploading artifacts there, because GHA doesn't store them for a long time:
mac64-installer (1).zip
windows32-installer (2).zip
windows64-installer (4).zip

[gaiksaya]

Hi @Yury-Fridlyand,

We need to on-board odbc to our 1-click release process.

Please go through the on-boarding doc and see if anything needs to be added from your end. All the above artifacts need to be generated as part of release-drafter workflow. https://github.com/opensearch-project/opensearch-build/blob/main/ONBOARDING.md#onboarding-to-universal--1-click-release-process

Also can you please add the target release date? So as to prioritize accordingly.
hanks!

[Yury-Fridlyand]

@gaiksaya Please see opensearch-project/sql-odbc#52

[acarbonetto]

@gaiksaya For release date, we would like to release before end of June. Would that date be possible to hit?

[gaiksaya]

Hi @acarbonetto ,

Yeah should be. Right now the blocker is the notarization process for macos artifact. This process is manual and we are looking if we can in anyway automate it. If not, we can proceed with manually notarizing the .pkg artifact for macos

[gaiksaya]

Hi @Yury-Fridlyand @acarbonetto ,

Just realized our code base is not integrated to sign macos asrtifacts here https://github.com/opensearch-project/opensearch-build/tree/main/src/sign_workflow
I'll try to get that in asap. @zelinh can take over this process next week if I am unable to complete it.
Thanks!

[acarbonetto]

@gaiksaya @zelinh any chance there's an ETA on this?
Are we still blocked on #3669?
Is there another blocked on the mac notarization too?

[gaiksaya]

Hey @acarbonetto ,

For signing part we are good. Closed #3669
Regarding notarization, it is still a blocker. But we do have a work around as I said which will include manually notarizing the artifacts after they are uploaded to artifacts.opensearch.org. Will wait for @zelinh to add more on this.
Thanks!

[acarbonetto]

@gaiksaya thanks so much!

[gaiksaya]

Update:
We tried manually notarizing the artifact, however it throws Status: invalid with logs as
"message": "The binary is not signed.",

We suspect the underlying signing is the culprit.
With current signing system, the pkgutil signature verification goes through however with codesign it fails even though the artifacts are signed.
Error:

codesign --verify --deep --verbose=4 --display OpenSearch-SQL-ODBC-Driver-64-bit-1.5.0.0-Darwin.pkg
OpenSearch-SQL-ODBC-Driver-64-bit-1.5.0.0-Darwin.pkg: code object is not signed at all

We are looking into it but might take some time as backend signing is handled by another team.

[gaiksaya]

Hi @Yury-Fridlyand @acarbonetto ,
Looks like we need to sign the actual binary and .pkg both.

I saw the signing and notarizing process we followed for https://github.com/opensearch-project/opensearch-cli

Below were the steps:

1. Built the artiacts (using https://github.com/opensearch-project/opensearch-cli/blob/main/release/build.sh)
2. Signed the binary, verified using codesign command.
3. Then packaged it using https://github.com/opensearch-project/opensearch-cli/blob/main/macos-pkg-builder/build-macos.sh
4. Signed the .pkg again which was verified using pkgutil.
5. Notarized the .pkg

I don't have much idea about odbc artifacts. Could you help here?

[Yury-Fridlyand]

We can try to adopt this for ODBC driver. Do you want to sign binary on GHA side (ODBC repo CI) or on Jenkins?

Another way is to unpack the installer, sign binary and pack back. Installer is just a set of nested zip, tar and cpio archives. Yes, I dislike this method too.

[gaiksaya]

We can try to adopt this for ODBC driver. Do you want to sign binary on GHA side (ODBC repo CI) or on Jenkins?

Signing needs to happen on Jenkins, due to authentication issues.

Another way is to unpack the installer, sign binary and pack back. Installer is just a set of nested zip, tar and cpio archives.

We tried that for windows artifacts with opensearch-net client. It was a disaster. Highly susceptible to change in artifacts. What would it take to pack the binary into an installer? Is it a simple command? Can it be a script residing in odbc repo?

So flow would be:
cut a tag -> upload mac binary, windows msi(no change). -> sign windows msi(no change) and sign mac binary then pack it and sign again, notarize it -> publish everything

[Yury-Fridlyand]

I see.
Current GHA produces mac64-build artifact which contains binaries only. I can modify release drafter and jenkinsfile to upload this artifact instead of mac installer.
Unfortunately, there are no resources (e.g. icons) required to build the installer. The signing backend (a macOS hosted jenkins agent) should check out repo and download them, or they should be uploaded as another artifact (for example, mac-installer-sources).
Does it make sense?
Extra software may need to be installed on that agent, for example, cmake.

[gaiksaya]

Makes sense! These are the current softwares installed on jenkins mac agent.
Let us know what all you need or feel free to create a PR to add those.
Thanks!

@peterzhuamazon @bbarani Is there a plan to use docker on macos agents too? If not immediately, maybe we need to create an issue to add these dependencies from agent node scripts to new docker image.

[Yury-Fridlyand]

Unfortunately, building ODBC installer for mac requires all driver dependencies. We can deliver them from GHA to Jenkins agent in a zip (pretty big one, ~150Mb), or build driver from the scratch on the agent. It requires extra software (libiodbc, vcpkg) and takes about 20 min.
It is possible to reduce size of that zip or modify building scripts (makefiles), but that would be longer than implementing options listed above ^.
What do you think?

[gaiksaya]

So those scripts are build into AMI (one time effort). Example currently each macos agent on jenkins is launched with this AMI https://github.com/opensearch-project/opensearch-ci/blob/main/lib/compute/agent-nodes.ts#L154
Can you provide the command to build the softwares and packges, etc?
20min will be one time to build that AMI. Once that is done we just replace the AMI id with new one and each jenkins launch will have all those softwares each time we launch.

[peterzhuamazon]

Makes sense! These are the current softwares installed on jenkins mac agent. Let us know what all you need or feel free to create a PR to add those. Thanks!

@peterzhuamazon @bbarani Is there a plan to use docker on macos agents too? If not immediately, maybe we need to create an issue to add these dependencies from agent node scripts to new docker image.

We dont have to use macos on docker and I dont even know if it is supported.
You can just add more executors on the mac agent.
Since mac1.metal instance is 12vCPUs and 32GB ram, I would say increase the executor number to 4 is good.

Thanks.

[peterzhuamazon]

Seems like macos on docker container it is supported to some degree but not sure about the requirements on hosts:
https://hub.docker.com/r/sickcodes/docker-osx

[Yury-Fridlyand]

I guess macos docker container could be started only on macos host (the same with macos VM).

@gaiksaya
Software required: curl, cmake, libiodbc (could be installed with brew) and vcpkg (installed with git clone and bash script).

To build the ODBC driver after checkout:

./build_mac_release64.sh

It produces binaries into build/odbc/lib.

Then, to build the installer:

cd cmake-build64
cmake ../src 
make
cpack .

It creates *.pkg installer into cmake-build64.

With all these, Jenkins don't need any artifacts from GHA. Only tag name or commit hash is required for checkout.

[gaiksaya]

@Yury-Fridlyand We will take the binary from GHA. Is that okay?

In that case we only need cmake to build the installer? Is this cmake same as https://github.com/opensearch-project/opensearch-ci/blob/main/packer/scripts/macos/macos-agentsetup.sh#L36

[Yury-Fridlyand]

Ok
But keep in mind that binary should be followed by all dependencies, cmake checks them. Complete archive with dependencies (and with their sources, unfortunately) weights for 150 Mb.

[gaiksaya]

[Offline discussion with @Yury-Fridlyand] Moving on with least resistance path. Windows artifacts promotion is already automated. We will look into macos automation later on. I'll create an issue detailing all the hurdles and possible solution, requirements.

Next steps:

1. I'll create a PR to remove the macos automation from the odbc repo.
2. Once merged, the maintainers can push 1.5.0.0 tag that will sign and publish the windows artifacts to artifacts.opensearch.org
3. Once done, @zelinh and me will cordinate to publish the macos artifacts using below steps:

git clone https://github.com/opensearch-project/sql-odbc.git
git checkout 1.5.0.0

4. Build ODBC

./build_mac_release64.sh

5. sign the binary located in build/odbc/lib
6. Build the installer:

cd cmake-build64
cmake ../src 
make
cpack .

7. sign the .pkg
8. Verify and then notarize
9. Upload to artifacts.opensearch.org
10. Notify maintainers and close the issue. I believe maintainers will take care of updating the website?
    Thanks!

[gaiksaya]

Hi @Yury-Fridlyand @acarbonetto Can you confirm that we can push tag based of main after we are ready for the release? In that way we would not have to be dependent on you for cutting the tag.
Sorry about the delay there have been few hiccups!

[gaiksaya]

Webhooks and other settings are in place to release the odbc artifacts. Please push the tag whenever you are ready. Also let us know by commenting on the issue here. We will proceed with signing and notarizing macos artifacts then.
Thanks!

[Yury-Fridlyand]

Good.
I cut the tag on ODBC repo and it triggered a jenkins job. Unfortunately, it failed. Could you please, have a look?

[gaiksaya]

Created a PR to fix the issue! I had fixed this on old one, forgot to apply to new jenkinsfile. Sorry about that!

[gaiksaya]

The release was successful: https://build.ci.opensearch.org/view/Release/job/sql-odbc-release/6/
Artifacts accessible at:

• http://artifacts.opensearch.org/opensearch-clients/odbc/opensearch-sql-odbc-driver-32-bit-1.5.0.0-Windows.msi
• http://artifacts.opensearch.org/opensearch-clients/odbc/opensearch-sql-odbc-driver-64-bit-1.5.0.0-Windows.msi

I'll be taking care of macos artifacts now using above procedure to sign and notarize now.

@Yury-Fridlyand Looks like there is some issue with the release name. Instead of 1.5.0.0 , it is named as Version 0.0.0. We can edit it manually for now but maybe look into it later? Also is anyone taking care of creating a pull request to update the website?

[Yury-Fridlyand]

Hi @gaiksaya,
Thank you for fixing this. I confirm that installers work, but they are signed by AWS, not by OpenSearch Project. Is it possible to change this in future?

I updated (renamed) release on ODBC repo. I'll update download links on the website once MAC installer ready.

[bbarani]

@Yury-Fridlyand We haven't migrated our signing system to use OpenSearch project certificates yet. I have opened an issue to track the progress of this change here. We will add this item to our roadmap as well.

[gaiksaya]

Mac artifact is signed, notarized and uploaded too: https://artifacts.opensearch.org/opensearch-clients/odbc/opensearch-sql-odbc-driver-64-bit-1.5.0.0-Darwin.pkg

Thanks!

[gaiksaya]

Closing this issue as sql-odbc is released successfully.
Thanks!