Kubernetes init database in a dirty way

Sometimes you need a dirty trick initialized the kubernetes running database with a set of sql statements (ddls). Here you are a simple example.

init-db:

kubectl exec -i --namespace timescaledb svc/csdb -c timescaledb -- \

        psql -U postgres -d cas --file=- <ddls/00-init-database.sql;

init-db-tables:

@for f in $(shell ls ddls/$(FILE_PREFIX)*.sql | grep -v 00-init); do \

kubectl exec -i --namespace timescaledb svc/csdb -c timescaledb -- \

            psql -U postgres -d cas --file=- <$$f; \

done

But what if you would like to variables in your script.

Here's a solution:

bash -c "cat <<EOF

$(< ddls/00-init-database.sql_tpl)

EOF" | kubectl exec -i --tty --namespace timescaledb ${PGMASTERPOD} -c timescaledb -- \

    psql -U postgres -d ${DBNAME}

Maven - Debugging the PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

From time to time that infamous exception attacks us. Usually, it is just as simple as import the valid key to your cacerts trustStore or specify the right path. But what if that does not help this time? After double check everything seems to be right. It's time to dive deeper. 

That simple debug steps make your live longer:

 

1. check the actual settings were applied: `-XshowSettings:properties` 
2. If still no idea what's wrong, try the ultimate weapon: `--Djavax.net.debug=all` - and be prepared for long log file ;). 

And couple more takeaways: 

1. The `.mavenrc` overrides whatever comes even in MAVEN_OPTS passed from environment.
2. The `${USER}` variable is not evaluated when put into `${PRJECT_DIR}/.mvn/jvm.config` file.

RabbitMQ - python publish to the channel required mTLS authentication

To make possible publish an event to the RabbitMQ channel secured with TLS certificate one requires delivering the `ssl_options` whilst creating a connection. The flag `ssl.CERT_REQUIRED` has to set for the `verify_mode` parameter of the default security context. Additional the `verify_flags` has to be set to `VERIFY_X509_TRUSTED_FIRST`. Moreover the cert_chain has to be populated with the valid certificate issued by CA specified when creating security context object. # Imports

import pika
import ssl

# Create method for obtaining connection

rabbit_url="170-187-131-61.ip.linodeusercontent.com"
path_to_cert=""../infra/secrets/rabbitmq/tls.crt"
path_to_key="../infra/secrets/rabbitmq/tls.key"
path_to_cafile="../infra/secrets/rabbitmq/ca.crt"
rabbituser, rabbitpass = 'rabbituser', 'rabbitp@ss'


def get_connection(kind="tls"):
   if kind == "tls":
        context = ssl.create_default_context(cafile=path_to_cafile)
        context.verify_mode = ssl.CERT_REQUIRED
        context.verify_flags = ssl.VERIFY_X509_TRUSTED_FIRST
        context.load_cert_chain(path_to_cert, path_to_key)
        ssl_options = pika.SSLOptions(context, rabbit_url)
        credentials = pika.PlainCredentials(rabbituser, rabbitpass)
        parameters = pika.ConnectionParameters(host=rabbit_url, 
                                               port=5671, 
                                               ssl_options=ssl_options,
                                               credentials=credentials)
        return pika.BlockingConnection(parameters)
    else:
        credentials = pika.PlainCredentials(rabbituser, rabbitpass)
        parameters = pika.ConnectionParameters('localhost', 5672, '', credentials)
        return pika.BlockingConnection(parameters)

# Use the connection and publish event

    logging.info(f"Processing command: {cmd}")
    with get_connection() as connection:
        with connection.channel() as channel:
          channel.queue_declare(queue='_events', durable=True)
          channel.basic_publish(
              exchange='',
              routing_key='_events',
              body=cmd,
              properties=pika.BasicProperties(
                  delivery_mode=2,  # make message persistent
              ))
          return " [x] Sent: %s" % cmd

SQL window functions by example

The following query computes number of metrics written in a given hour

select time, workflow, metrics_written, 
       CAST(TO_CHAR(time, 'YYMMddHH24') as BIGINT) as bin, 
       min(metrics_written) over w, max(metrics_written) over w
  from internal_write
 where workflow = 'workflow_name'
   and output = 'sql'
 window w as (PARTITION BY CAST(TO_CHAR(time, 'YYMMddHH24') as BIGINT) 
                  ORDER BY time DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  order by time desc

This one in turn shows number of metrics written within passing 5 minutes window

select time, metrics_written, 
       max(metrics_written) over w - min(metrics_written) over w as metrics_written_within_5_minutes
  from internal_write
 where workflow = 'workflow_name'
   and output = 'sql'
window w as (order by time range (interval '5 min') preceding)
 order by time desc
 

How to intercept requests with lua script in Istio service mesh

Add custom header to the responses comming out the given Kubernetes application

apiVersion: networking.istio.io/v1alpha3
kind: EnvoyFilter
metadata:
  name: add-custom-header-envoyfilter
  namespace: istio-system
spec:
  workloadSelector:
    labels:
      app: httpd-proxy
  configPatches:
  - applyTo: HTTP_FILTER
    match:
      context: SIDECAR_INBOUND
      listener:
        # portNumber: 80
        filterChain:
          filter:
            name: envoy.filters.network.http_connection_manager
            subFilter:
              name: envoy.filters.http.router
    patch:
      operation: INSERT_BEFORE
      value:
        name: envoy.filters.http.lua
        typed_config:
          "@type": "type.googleapis.com/envoy.extensions.filters.http.lua.v3.Lua"
          inlineCode: |
            function envoy_on_response(response_handle)
              response_handle:logDebug("Adding custom header to the response")
              response_handle:headers():add("X-Custom-Header", "1.2.3.4")
            end

The above works on the response. To modify the Lua `inlineCode` should look like this

  function envoy_on_request(request_handle)
  	request_handle:logInfo("Received request for " .. request_handle:headers():get(":path"))
  end

Note that by default the istio logging level is set to warning. You need to turn loggin to info to make it appear in the log file.

Oneliner for generating a self-signed x.509 certificate for scripting purposes

Purpose

There are cases when we'd need a quick way to generate x.509 certificate for scripting adhoc purposes without a need to create a CSR file or putting the values from the terminal by hand.

Prerequisites

Tested with Centos 7/stream8. $yum install openssl -y

Solution

openssl req -subj '/CN=lol.mazia.rz/O=MZIARZ/C=US' -new -newkey rsa:2048 -sha256 -days 365 -nodes -x509 \
      -keyout /etc/httpd/certs/private-key.pem \
      -out /etc/httpd/certs/cert.pem

And here you go. Have fun.

Pandas write integer dataframe column as a timestamp into the database

To display DataFrame's datatypes use dtypes property.

> print(df.dtypes)

last_login    float64
created_at      int64

In order to cast the `float64` or `int64` column into the `datetime64` one the to_datetime method has to be used. Note that specifying the time unit may be needed to make the conversion correct. Assuming our values contain a UNIX timestamps in seconds the `unit="s"` extra parameter will be required.

df['last_login'] = pd.to_datetime(df['last_login'], unit="s")
df['created_at'] = pd.to_datetime(df['created_at'], unit="s")

Finnaly we receive the following types:

> print(df.dtypes)

last_login    datetime64[ns]
created_at    datetime64[ns]

Now the timestamps in the database will be valid instead of '0000-00-00 00:00:00' we spotted without valid conversion.