Configuring CockroachDB with Active Directory

This post covers the basics of integrating CockroachDB with Active Directory. AD is the commercial twin of Kerberos provided by Microsoft.

Today we’ll talk about CockroachDB integration with Active Directory. Cockroach is based on the security services API GSSAPI… Cockroach currently only supports user matching. But the synchronization of users of an organizational unit (OU) AD with roles in Cockroach is no longer there.

My test environment consists of an Active Directory controller in a Virtual Box VM running Windows Server 2016 and a Vagrant VM running CentOS 7 running CockroachDB. Virtual machines are networked and visible only to the host and to each other (host-only). This is critical to my configuration because it gives the Cockroach node the ability to communicate with AD via
port 88

Requirements:

  1. Active Directory domain controller.
  2. Linux OS, in my case RHEL7 and CockroachDB 20.1.1.

The necessary conditions:

  • I am using an evaluation version of Windows Server 2016. A trial version with a grace period of 180 days can be downloaded here
  • For custom installation of Windows Server on a Virtual Box VM, I used, in particular, here this guide
  • Install VirtualBox Guest Additions by this instruction
  • Open AD and host access to one of directory… You will need it to copy key files (keytabs) to Cockroach nodes.
  • Change machine computer name from AD to easy-to-read. For me, for example, this is adserver.
  • The AD server and Cockroach nodes need to be synchronized with time, date, and time zone. When working with Kerberos, this goes without saying, but it is worth recalling here too.
  • Change the Windows IP address to the subnet of the CockroachDB host (s).
  • Add Cockroach nodes to hosts file on Windows (this is an optional step).
  • Install an Active Directory domain controller using leadership

The pictures below are for your reference – by following the steps in the tutorials, you probably have already achieved the desired result.

Installing Windows Server

Changing the name of a Windows computer

Checking for a new computer name

Synchronization by time, date, time zone

Change AD subnet to the same as CockroachDB

Optional: add CockroachDB hosts to the AD server hosts file

At this stage, you can already check the connection with CentOS with the ping command:

Similarly, verification can be done from a CentOS machine after filling in the / etc / hosts file.

127.0.0.1   	node.example.com    	node
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1     	localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.33.15   adserver.example.com	adserver
[vagrant@node ~]$ ping adserver.example.com
PING adserver.example.com (192.168.33.15) 56(84) bytes of data.
64 bytes from adserver.example.com (192.168.33.15): icmp_seq=1 ttl=128 time=0.369 ms
64 bytes from adserver.example.com (192.168.33.15): icmp_seq=2 ttl=128 time=0.511 ms
64 bytes from adserver.example.com (192.168.33.15): icmp_seq=3 ttl=128 time=0.430 ms

As a safety net, let’s make sure that you can connect to the AD server on port 88. This will require the telnet packages to be installed:

sudo yum install -y telnet
telnet adserver.example.com 88
Trying 192.168.33.15...
Connected to adserver.example.com.
Escape character is '^]'.

Now let’s move on to adding a service principal to AD. Generate a key file and configure the krb5.conf file on the CentOS machine.

On a machine with an AD controller, navigate to the Active Directory Users and Computers console.

Add a new user by right-clicking on Users under the domain – in my case it looks like this:

Finally, select the desired password settings and click Finish:

It is also important to check the two Kerberos related user settings. In the Windows Server user interface, I found it difficult to spot them. They are located on the user properties page, the Account tab. To find them in the list, you may need to scroll all the way to the bottom of the checkbox area.

To complete the step, apply the changes with the Apply button.

Now we can map the Service Principal Name (SPN) and create a key file. The command line or PowerShell is suitable for this task. This is where the AD utility called ktpass:

ktpass -out node.keytab -princ postgres/node.example.com@EXAMPLE.COM -mapUser pguser@EXAMPLE.COM -mapOp set -pType KRB5_NT_PRINCIPAL -crypto AES256-SHA1 -pass CRDB123?

This command creates a key file named

node.keytab

by matching the service principal name

postgres

with the FQDN of the Cockroach node

node.example.com@EXAMPLE.COM

as well as an AD user

pguser

with the service principal name. We indicate the encryption mode

AES256-SHA1

, and finally pass the password to the newly created SPN.

Generating a Key File and Mapping an SPN to a Principal

We can also check the SPN with the following command:

setspn -l pguser

At this point, we have a key file that we can use to connect to AD from a CentOS machine. Let’s copy it to the hosts file in Cockroach.

Before integrating with AD in the hosts file on CentOS, you need to perform a number of preliminary steps. First, let’s make sure that the time, date and time zone settings are correct:

timedatectl
timedatectl list-timezones | grep New_York
[vagrant@node ~]$ timedatectl
  	Local time: Wed 2020-06-03 17:00:01 UTC
  Universal time: Wed 2020-06-03 17:00:01 UTC
    	RTC time: Wed 2020-06-03 16:59:59
   	Time zone: UTC (UTC, +0000)
 	NTP enabled: yes
NTP synchronized: yes
 RTC in local TZ: no
  	DST active: n/a
[vagrant@node ~]$ timedatectl list-timezones | grep New_York
America/New_York
[vagrant@node ~]$ sudo timedatectl set-timezone America/New_York
[vagrant@node ~]$ timedatectl
  	Local time: Wed 2020-06-03 13:01:06 EDT
  Universal time: Wed 2020-06-03 17:01:06 UTC
    	RTC time: Wed 2020-06-03 17:01:05
   	Time zone: America/New_York (EDT, -0400)
 	NTP enabled: yes
NTP synchronized: yes
 RTC in local TZ: no
  	DST active: yes
 Last DST change: DST began at
              	Sun 2020-03-08 01:59:59 EST
              	Sun 2020-03-08 03:00:00 EDT
 Next DST change: DST ends (the clock jumps one hour backwards) at
              	Sun 2020-11-01 01:59:59 EDT
              	Sun 2020-11-01 01:00:00 EST

Install the package

krb5-workstation

and fill it with properties unique to AD:

yum install -y krb5-workstation

Conduct file configuration

/etc/krb5.conf

by specifying the following properties:

[logging]
 default = FILE:/var/log/krb5libs.log
 kdc = FILE:/var/log/krb5kdc.log
 admin_server = FILE:/var/log/kadmind.log
 
[libdefaults]
 default_realm = EXAMPLE.COM
 
[realms]
 EXAMPLE.COM = {
  kdc = adserver.example.com
  admin_server = adserver.example.com
  default_domain = example.com
 }
 
[domain_realm]
 .example.com = EXAMPLE.COM
 example.com = EXAMPLE.COM

Change the permissions in the key file:

chmod 600 node.keytab

Set the variable

KRB5_KTNAME

the value corresponding to the location of the given file:

export KRB5_KTNAME=node.keytab

In theory, now you can authenticate in AD under the name

pguser

:

[vagrant@node ~]$ kinit pguser
Password for pguser@EXAMPLE.COM:
[vagrant@node ~]$ klist
Ticket cache: FILE:/tmp/krb5cc_1000
Default principal: pguser@EXAMPLE.COM
 
Valid starting   	Expires          	Service principal
06/03/2020 13:23:06  06/03/2020 23:23:06  krbtgt/EXAMPLE.COM@EXAMPLE.COM
    	renew until 06/04/2020 13:23:06

Let’s check a couple more things in the key file to avoid hours of agony in the future. Trust my bitter experience!

According to Kerberos requirements, the number KVNO for a subject in a system running a Unix-based OS must be greater than or equal to 3. You can check it like this:

kvno pguser@EXAMPLE.COM
pguser@EXAMPLE.COM: kvno = 3

Let’s make sure the KVNO of our SPN matches the subject:

kvno postgres/node.example.com@EXAMPLE.COM
[vagrant@node ~]$ kvno postgres/node.example.com@EXAMPLE.COM
postgres/node.example.com@EXAMPLE.COM: kvno = 3

You can also check the correspondence of the entry in the key file:

[vagrant@node ~]$ klist -kt node.keytab
Keytab name: FILE:node.keytab
KVNO Timestamp       	Principal
---- ------------------- ------------------------------------------------------
   3 12/31/1969 19:00:00 postgres/node.example.com@EXAMPLE.COM

The same is verified with

ktutil

:

[vagrant@node ~]$ ktutil
ktutil:  read_kt node.keytab
ktutil:  list
slot KVNO Principal
---- ---- ---------------------------------------------------------------------
   1	3	postgres/node.example.com@EXAMPLE.COM

Although we have already checked both KVNO numbers, let’s repeat the procedure on the AD server to be on the safe side.

To get the KVNO value, run the following command in PowerShell:

Get-ADUser pguser -property msDS-KeyVersionNumber

Once all the values ​​are the same, we can proceed to configuring the GSSAPI in Cockroach.

Install CockroachDB:

COCKROACH_VERSION=v21.2.0
wget -qO- https://binaries.cockroachdb.com/cockroach-$COCKROACH_VERSION.linux-amd64.tgz | tar  xvz
sudo cp -i cockroach-$COCKROACH_VERSION.linux-amd64/cockroach /usr/local/bin/
cockroach version
Build Tag:	v21.2.0
Build Time:   2021/11/15 14:00:58
Distribution: CCL
Platform: 	linux amd64 (x86_64-unknown-linux-gnu)
Go Version:   go1.16.6
C Compiler:   Clang 10.0.0
Build SHA-1:  6123c0c73ff0eea223cfd25e1e557648413126f8
Build Type:   release

Run protected

cluster

Create certificates. The procedure described in the documentation is fine for our purposes. I just pass the additional DNS names to the certs command:

HOSTNAME="node.example.com node 192.168.33.10"
mkdir certs my-safe-directory
cockroach cert create-ca --certs-dir=certs --ca-key=my-safe-directory/ca.key
cockroach cert create-node $HOSTNAME --certs-dir=certs --ca-key=my-safe-directory/ca.key
openssl x509 -in certs/node.crt -text | grep "Subject Alternative Name" -A 1
cockroach cert create-client root --certs-dir=certs --ca-key=my-safe-directory/ca.key
[vagrant@node ~]$ openssl x509 -in certs/node.crt -text | grep "Subject Alternative Name" -A 1
        	X509v3 Subject Alternative Name:
            	DNS:node.example.com, DNS:node, IP Address:192.168.33.10

Launch Cockroach in safe mode:

cockroach start --certs-dir=certs --store=node1 --listen-addr=node.example.com:26257 --http-addr=node.example.com:8080 --join=node.example.com:26257,node.example.com:26258,node.example.com:26259 --background
cockroach start --certs-dir=certs --store=node2 --listen-addr=node.example.com:26258 --http-addr=node.example.com:8081 --join=node.example.com:26257,node.example.com:26258,node.example.com:26259 --background
cockroach start --certs-dir=certs --store=node3 --listen-addr=node.example.com:26259 --http-addr=node.example.com:8082 --join=node.example.com:26257,node.example.com:26258,node.example.com:26259 --background

We initiate the cluster:

[vagrant@node ~]$ cockroach init --certs-dir=certs --host=node.example.com:26257
Cluster successfully initialized

We connect to the database:

cockroach sql --certs-dir=certs --host=node.example.com

We activate the corporate license. At the time of this writing, the GSSAPI is only available in the corporate version:

SET CLUSTER SETTING cluster.organization = 'Acme Company';
SET CLUSTER SETTING enterprise.license="xxxxxxxxxxxx";

Turn on GSSAPI – for everyone except the root user. It will still connect using the root certificate:

SET cluster setting server.host_based_authentication.configuration = 'host all all all gss include_realm=0';

We create an ordinary user and give him the rights:

CREATE USER pguser;
GRANT ALL ON DATABASE defaultdb TO pguser;
q

It remains only to execute the command

kinit

on behalf of

pguser

… If you remember, we already did this, but to complete the process, the step must be repeated:

kdestroy -A
kinit pguser
klist

Finally, you need to install the client.

psql

, insofar as

cockroach.CLI

does not support GSSAPI.

In my opinion, it is preferable to use the psql client version 9.5, since CockroachDB supports this wired protocol. Unfortunately, CentOS 7 ships with version 9.2, and to install the version we need we will follow the steps outlined here

Disable postgresql in sections [base] and [updates] file /etc/yum.repos.d/CentOS-Base.repo using the exclude = postgresql * command:

[base]
name=CentOS-$releasever - Base
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os&infra=$infra
#baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
exclude=postgresql*
 
#released updates
[updates]
name=CentOS-$releasever - Updates
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates&infra=$infra
#baseurl=http://mirror.centos.org/centos/$releasever/updates/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
exclude=postgresql*

Then we will execute the instructions given on

link

… I choose version 9.5.

yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm -y
yum install postgresql95 -y

Finally, connect to CockroachDB as user pguser:

psql "postgresql://node.example.com:26257/defaultdb?sslmode=require" -U pguser
psql (9.5.22, server 9.5.0)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
Type "help" for help.
 
defaultdb=>

What if you have many nodes, as is often the case with CockroachDB? You need to add all the nodes to the member list in the key file. Since I only have one node –

node.example.com

– I will indicate it via the IP address to illustrate the next step:

My IP address is 192.168.33.10. Team ktpass to add subjects will have a slightly different look:

ktpass -out ip.keytab -princ postgres/192.168.33.10@EXAMPLE.COM -mapUser pguser@EXAMPLE.COM -mapOp add -pType KRB5_NT_PRINCIPAL -crypto AES256-SHA1 -pass CRDB123?

Note that I am using a new key file so as not to overwrite the existing one. We can use it as an addition to the existing one or combine them later. Also, I changed

-mapOp

on the

add

With

set

… This is important to avoid replacing the current record with a new one. Instead, an add-on is created:

When executing the command

setspn -l user

both entries will be displayed:

Let’s check the KVNO as we made changes to the subject parameters:

Notice the KVNO number has increased.

Now let’s copy the key file to host CockroachDB and check if everything is correct there:

[vagrant@node ~]$ klist -kt ip.keytab
Keytab name: FILE:ip.keytab
KVNO Timestamp       	Principal
---- ------------------- ------------------------------------------------------
   4 12/31/1969 19:00:00 postgres/192.168.33.10@EXAMPLE.COM

The KVNO values ​​are the same:

[vagrant@node ~]$ kvno pguser@EXAMPLE.COM
pguser@EXAMPLE.COM: kvno = 4
[vagrant@node ~]$ kvno postgres/192.168.33.10@EXAMPLE.COM
postgres/192.168.33.10@EXAMPLE.COM: kvno = 4
[vagrant@node ~]$ kvno postgres/node.example.com@EXAMPLE.COM
postgres/node.example.com@EXAMPLE.COM: kvno = 3

The KVNO for node.example.com is still 3 because it points to the old key file – as it should:

[vagrant@node ~]$ klist -kt ip.keytab
Keytab name: FILE:ip.keytab
KVNO Timestamp       	Principal
---- ------------------- ------------------------------------------------------
   4 12/31/1969 19:00:00 postgres/192.168.33.10@EXAMPLE.COM
[vagrant@node ~]$ klist -kt node.keytab
Keytab name: FILE:node.keytab
KVNO Timestamp       	Principal
---- ------------------- ------------------------------------------------------
   3 12/31/1969 19:00:00 postgres/node.example.com@EXAMPLE.COM

Let’s merge the two key files and try again:

[vagrant@node ~]$ ktutil
ktutil:  read_kt ip.keytab
ktutil:  read_kt node.keytab
ktutil:  list
slot KVNO Principal
---- ---- ---------------------------------------------------------------------
   1	4   	postgres/192.168.33.10@EXAMPLE.COM
   2	3	postgres/node.example.com@EXAMPLE.COM
ktutil:  write_kt postgres.keytab
ktutil:  exit
[vagrant@node ~]$ klist -kt postgres.keytab
Keytab name: FILE:postgres.keytab
KVNO Timestamp       	Principal
---- ------------------- ------------------------------------------------------
   4 06/03/2020 14:21:20 postgres/192.168.33.10@EXAMPLE.COM
   3 06/03/2020 14:21:20 postgres/node.example.com@EXAMPLE.COM
chmod 600 postgres.keytab
export KRB5_KTNAME=postgres.keytab

A rolling restart may be required for the cluster to start working with the new key file:

vagrant@node ~]$ psql "postgresql://node.example.com:26257/defaultdb?sslmode=require" -U pguser
psql (9.5.22, server 9.5.0)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
Type "help" for help.
 
defaultdb=>

We do have a subject with an IP address of 192.168.33.10. Let’s try to connect to it:

[vagrant@node ~]$ psql "postgresql://192.168.33.10:26257/defaultdb?sslmode=require" -U pguser
psql: could not connect to server: Connection refused
    	Is the server running on host "192.168.33.10" and accepting
    	TCP/IP connections on port 26257?

The reason for the error is that the node was started using the command –

-listen-addr=node.example.com:26257

… To restart it I enter

--listen-addr=192.168.33.10:26257

We connect again:

[vagrant@node ~]$ psql "postgresql://192.168.33.10:26257/defaultdb?sslmode=require" -U pguser
psql (9.5.22, server 9.5.0)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
Type "help" for help.
 
defaultdb=>

Happened! This IP can be used for a load balancer too.

Important note

Using the mode

sslmode=require

carries the risk of a MITM (Man In The Middle) attack. Therefore, it is recommended to use the values

verify-ca

or

verify-full

verify-ca safer in terms of MITM attacks, as it verifies the security of the server through the issuer of the certificate:

psql "postgresql://node.example.com:26257/defaultdb?sslmode=verify-ca&sslrootcert=certs/ca.crt" -U pguser

verify-full

in addition to this, it also checks the Common Name attribute for a match with the computer name:

psql "postgresql://node.example.com:26257/defaultdb?sslmode=verify-full&sslrootcert=certs/ca.crt" -U pguser

For more details, see

official documentation


A UFO flew in and left here promotional codes for the readers of our blog:

15% for all VDS tariffs (except for the Warming up tariff) – HABRFIRSTVDS

20% off AMD Ryzen and Intel Core Dedicated Servers HABRFIRSTDEDIC

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *