Skip to main content
View rawEdit

Masking sensitive data in PostgreSQL logs when using CI Observer

Configure masking for the PostgreSQL log​

When DBLab's CI Observer is used for automated testing of database migrations, it stores the PostgreSQL log in DBLab Platform's centralized storage. You can optionally configure masking rules for sensitive data in the PostgreSQL log. These rules are applied continuously, before any PostgreSQL log entries are sent to the Platform's storage.

You can define masking rules in the form of regular expressions. To do it, open the DBLab Engine configuration file (usually, ~/.dblab/engine/configs/server.yml; see config file examples here) and define subsection replacementRules in the section observer. A basic example:

observer:
  replacementRules:
    "(?:[0-9]{1,3}\\.){3}[0-9]{1,3}": "*.*.*.*" # mask IP address
    "[a-z0-9._%+\\-]+(@[a-z0-9.\\-]+\\.[a-z]{2,4})": "***$1" # mask email address

Before masking:

ip: 127.0.0.1, email: '[email protected]'

After masking:

ip: *.*.*.*, email: '***@example.com'

You can specify as many masking rules as you need, in key-value format. In the example above, two rules are specified: one is for masking all IP addresses, and another to mask all emails.

Each masking rule consists of a key and a value:

  • Keys are regular expressions (see details below)
  • Values are replacement templates, where substitution is supported ($1, $2, etc.)

Use a backslash (\) to escape special characters: https://yaml.org/spec/1.2/spec.html#id2788097.

caution

When many sophisticated regular expressions are used, one might expect a slowdown of Postgres log processing. Try to define as few rules as possible, as simple as possible.

How it works​

Replacement rules are applied to all log fields of the incoming PostgreSQL CSV log lines that can contain some sensitive data:

  • message
  • detail
  • hint
  • internal_query
  • query

Regular expressions​

The accepted regular expression syntax is the same general syntax used by Perl, Python, and other languages. You can find syntax details here: https://github.com/google/re2/wiki/Syntax.

In a template, a variable is denoted by a substring of the form $name or ${name}, where name is a non-empty sequence of letters, digits, and underscores. A purely numeric name like $1 refers to a submatch with the corresponding index; other names refer to capturing parentheses named with the (?P<name>...) syntax. A reference to an out-of-range or unmatched index or a name that is not present in the regular expression is replaced by an empty string.

To insert a literal $ in the output, use $$ in the template.