This documentation describes an old release, version 3.6.6. Documentation for the latest release, 3.7.2, can be found here.

Connect Looker to Zuar Runner¶

Use the following script to download the cert:

#!/usr/bin/env python3

import argparse
import socket
import ssl
import struct
import subprocess
import sys


def main():
    args = get_args()
    target = (args.fqdn, args.port)
    try:
        sock = socket.create_connection(target)
        certificate_as_pem = get_certificate_from_socket(sock, args.fqdn)
        print(certificate_as_pem.decode('utf-8'))
    except Exception as exc:
        sys.stderr.write(f'Something failed while fetching certificate: {exc}\n')
        sys.exit(1)
    finally:
        sock.close()


def get_args():
    parser = argparse.ArgumentParser(description='Fetch and print the SSL certificate of a PostgreSQL server.')
    parser.add_argument('-f', '--fqdn', required=True, help='Fully Qualified Domain Name or IP address')
    parser.add_argument('-p', '--port', type=int, default=5432, help='Port number (default: 5432)')
    return parser.parse_args()


def get_certificate_from_socket(sock,hostname):
    request_ssl(sock)
    ssl_context = get_ssl_context()
    sock = ssl_context.wrap_socket(sock, server_hostname=hostname)
    sock.do_handshake()
    certificate_as_der = sock.getpeercert(binary_form=True)
    return encode_der_as_pem(certificate_as_der)


def request_ssl(sock):
    version_ssl = postgres_protocol_version_to_binary(1234, 5679)
    length = struct.pack('!I', 8)
    packet = length + version_ssl

    sock.sendall(packet)
    data = read_n_bytes_from_socket(sock, 1)
    if data != b'S':
        raise Exception('Backend does not support TLS')

def get_ssl_context():
    ctx = ssl.create_default_context()
    ctx.check_hostname = False
    ctx.verify_mode = ssl.CERT_NONE
    return ctx


def encode_der_as_pem(cert):
    cmd = ['openssl', 'x509', '-inform', 'DER']
    process = subprocess.Popen(cmd, stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    stdout, stderr = process.communicate(cert)
    if stderr:
        raise Exception(f'OpenSSL error when converting cert to PEM: {stderr.decode("utf-8")}')
    return stdout.strip()


def read_n_bytes_from_socket(sock, n):
    buf = bytearray(n)
    view = memoryview(buf)
    while n:
        nbytes = sock.recv_into(view, n)
        view = view[nbytes:]
        n -= nbytes
    return buf


def postgres_protocol_version_to_binary(major, minor):
    return struct.pack('!I', major << 16 | minor)


if __name__ == '__main__':
    main()

Save script above as pg_get_server_cert.py
python3 pg_get_server_cert.py -f <Runner URL> > <Runner URL>_Postgres_Cert.pem
Browse to https://lookerstudio.google.com/datasources/create
Search for PostgreSQL and click to select.
Enter the Host name: host name, port: 5432, database: analytics, Username: admin, Password:
Check Enable SSL
Click the upload symbol in the PostgreSQL SSL Configuration Files section and select the file you created above
Click the “AUTHENTICATE” button