postgresqlsslamazon-s3openshiftself-signed-certificate

PostgreSQL `aws_s3` Extension: SSL Certificate Verification Failed with Self-Signed Certificate

I'm attempting to import data into a PostgreSQL table from a CSV file stored in a NooBaa S3 bucket. I'm using the aws_s3 extension in PostgreSQL for this task. However, I'm encountering an SSL certificate verification error, even after providing a custom CA certificate. Here are the details of my setup:

PostgreSQL Setup:

  1. Environment: Running PostgreSQL inside an OpenShift pod.
  2. AWS S3 Extension: Using aws_s3 and aws_commons extensions for data import.
  3. NooBaa S3 Endpoint: I have a custom S3-compatible endpoint provided by NooBaa.

Steps I Followed:

  1. Uploaded the CA Certificate: The custom CA certificate is uploaded to /tmp/ca.crt inside the PostgreSQL pod.
  2. Configured PostgreSQL for SSL: 
    SET aws.s3.ssl_verify_cert = 1;
SET SESSION aws_s3.ssl_cert_file TO '/tmp/ca.crt';
SET SESSION aws_s3.endpoint_url TO 'https://s3.my-company.com';
  3. Import Command: 
    SELECT aws_s3.table_import_from_s3(
  'esession_end',  -- Target table name
  '',              -- Column list (empty means import all columns)
  '(format csv, header true)',  -- Import options
  aws_commons.create_s3_uri(
    'my-sample-bucket',        -- Bucket name
    'sample_data.csv',         -- CSV file name
    'noobaa'                   -- Region or custom endpoint
  ),
  aws_commons.create_aws_credentials(
    'ACCESS_KEY_ID', 
    'SECRET_ACCESS_KEY',
    ''
  )
);

Error Received:

ERROR:  spiexceptions.ExternalRoutineException: botocore.exceptions.SSLError: SSL validation failed for https://s3.my-company.com/my-sample-bucket/sample_data.csv [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: self-signed certificate in certificate chain (_ssl.c:992)
CONTEXT:  Traceback (most recent call last):
  PL/Python function "table_import_from_s3", line 7, in <module>
    return plan.execute(
PL/Python function "table_import_from_s3"

Debugging Steps Taken:

Questions:

  1. Is there a specific configuration needed in PostgreSQL or the aws_s3 extension to correctly handle self-signed certificates for a NooBaa S3-compatible endpoint?
  2. Has anyone successfully used the aws_s3 extension with a NooBaa endpoint, and if so, what configurations did you use?
  3. Are there any additional debugging steps or tools I can use to further diagnose this SSL certificate verification issue?

Any help or guidance would be greatly appreciated!

Solution

  • Handling SSL Certificate Issues, Root Privileges, and Security Context in OpenShift for PostgreSQL aws_s3 Extension

    To resolve the SSL certificate verification issue when connecting to a NooBaa S3-compatible endpoint in PostgreSQL, you’ll need to do the following:

    Step 1: Obtain and Install SSL Certificates

    First, gather the SSL certificates required for your NooBaa endpoint. Since there’s a chain of certificates, you need to concatenate all of them into one file.

    Get SSL Certificates

    Run the following command to retrieve the SSL certificates:

    echo | openssl s_client -showcerts -servername s3.staging.apps.product.ibm.com -connect s3.staging.apps.product.ibm.com:443

    Concatenate Certificates

    Once you have all the certificates, concatenate them into a single file. On Linux, name the file noobaa-ca.crt, and for macOS, name it noobaa-ca.pem. The file should look something like this:

    -----BEGIN CERTIFICATE-----
<certificate 1 content>
-----END CERTIFICATE-----
-----BEGIN CERTIFICATE-----
<certificate 2 content>
-----END CERTIFICATE-----

    Upload to the OpenShift Pod

    To make the certificate available inside the PostgreSQL container:

    1. Copy the file into the PostgreSQL pod:

      oc cp noobaa-ca.crt <postgres-pod-name>:/tmp/

    2. If you encounter permission issues, mount the file using a ConfigMap:

      oc create configmap custom-ca-cert --from-file=noobaa-ca.crt

    Step 2: Verify the Certificate Using AWS CLI

    Now that you have the certificate, you can interact with the AWS CLI from your host computer (not inside OpenShift yet). Run the following command to ensure there are no SSL warnings:

    aws s3 ls --endpoint-url https://s3.staging.apps.product.ibm.com --ca-bundle noobaa-ca.pem

    This verifies that the system can interact with the S3 endpoint securely using the noobaa-ca.pem file.

    Explanation of the SSL Certificate Chain

    How SSL Certificates Work:

    SSL certificates enable secure communication between clients (e.g., your browser or AWS CLI) and servers by ensuring:

    Certificate Chain:

    SSL certificates are part of a certificate chain, which includes:

    1. Leaf Certificate: The server’s certificate (e.g., *.staging.apps.product.ibm.com).
    2. Intermediate Certificates: Issued by trusted certificate authorities (CAs).
    3. Root Certificate: The top-level certificate from a trusted authority, typically pre-installed in operating systems.

    In your case, the server presented a certificate (*.staging.apps.product.ibm.com) signed by an internal CA (ibm.com). Since this CA isn’t in the public trust store (like those maintained by browsers or OSes), your system doesn’t trust it by default, resulting in the SSL error.

    By creating the noobaa-ca.pem file, we manually instructed the AWS CLI to trust the entire certificate chain.

    Step 3: Get Root Privileges in the OpenShift Container

    OpenShift restricts running containers as the root user by default. To bypass this, you need to define and apply a custom Security Context Constraint (SCC).

    Define a Custom SCC (root-scc.yaml):

    apiVersion: security.openshift.io/v1
kind: SecurityContextConstraints
metadata:
  name: root-scc
allowPrivilegedContainer: true
allowHostDirVolumePlugin: false
allowHostPorts: false
allowHostNetwork: false
allowHostPID: false
allowHostIPC: false
allowRunAsUser: true
fsGroup:
  type: RunAsAny
runAsUser:
  type: RunAsAny
seLinuxContext:
  type: RunAsAny
supplementalGroups:
  type: RunAsAny
users:
- system:serviceaccount:postgres-custom:default

    This SCC allows the container to run as any user, including root.

    Apply the SCC:

    oc adm policy add-scc-to-user root-scc -z default -n <your-namespace>

    Now, the PostgreSQL pod can start as the root user in your current namespace.

    Step 4: Mount the SSL Certificate in PostgreSQL Deployment

    Next, you need to configure the PostgreSQL deployment to mount the certificate and update the system’s trusted certificates.

    Create the ConfigMap:

    If you haven’t already created the ConfigMap, do so now:

    oc create configmap custom-ca-cert --from-file=noobaa-ca.crt

    Modify the Deployment:

    In your deployment file, modify the volumeMounts and volumes sections to mount the certificate. Here’s an example:

          containers:
      - name: postgres
        image: postgres:16
        securityContext:
          runAsUser: 0  # Run the container as the root user
        volumeMounts:
        - name: custom-ca-cert
          mountPath: /usr/local/share/ca-certificates/noobaa-ca.crt
          subPath: noobaa-ca.crt  # Mount the single file from the ConfigMap
      volumes:
      - name: custom-ca-cert
        configMap:
          name: custom-ca-cert  # The ConfigMap we created earlier

    Update the System’s Trusted Certificates:

    Inside the container, run the following command to update the system’s trusted certificates:

    update-ca-certificates

    If you encounter any issues, try running it as root:

    sudo update-ca-certificates

    Step 5: Import Data from S3 Using aws_s3 Extension

    Now that everything is set up, you can run the import command in PostgreSQL to load data from the S3 bucket into your table:

    SET SESSION aws_s3.endpoint_url TO 'https://s3.my-company.com';

SELECT aws_s3.table_import_from_s3(
  'esession_end',  -- Target table name
  '',              -- Column list (import all columns)
  '(format csv, header true)',  -- Import options
  aws_commons.create_s3_uri(
    'my-sample-bucket',        -- Bucket name
    'sample_data.csv',         -- CSV file name
    'noobaa'                   -- Region or custom endpoint
  ),
  aws_commons.create_aws_credentials(
    'ACCESS_KEY_ID', 
    'SECRET_ACCESS_KEY',
    ''
  )
);

    This command connects to your S3 bucket via the aws_s3 extension, using the SSL certificate chain you’ve set up and the root user privileges to handle the file system within the container.