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