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.