Import Data Into CockroachDB With Kerberos Authentication

A customer had asked how to leverage bulk utilities in CockroachDB while authenticated via Kerberos.

Artem Ervits

CORE ·

Jan. 26, 22 · Database Zone · Tutorial

Like (2)

Save

4.48K Views

Articles Covering CockroachDB and Kerberos

I find the topic of Kerberos very interesting and my colleagues commonly refer to me for help with this complex topic. I am by no means an expert at Kerberos, I am however familiar enough with it to be dangerous. That said, I've written multiple articles on the topic which you may find below:

Part 1: CockroachDB with MIT Kerberos
Part 2: CockroachDB With Active Directory
Part 3: CockroachDB With MIT Kerberos and Docker Compose

I was recently asked by a customer whether GSSAPI gets in the way of doing a table import in CockroachDB. The short answer is it shouldn't as GSSAPI is abstracted from any bulk-io operations. I've previously written articles on doing an import into Cockroach, here and here and encourage you to review those articles. So today we're going to focus on specifically the import with Kerberos.

We will need an instance of CockroachDB, Kerberos and a GSSAPI compatible client like psql. If you've read my previous articles in the series, I already have a repo with docker compose environment where we can demo this.

Copy the cockroach-gssapi directory to your machine:

git clone https://github.com/dbist/cockroach-docker

Create a directory called import in the root of the project and map it in docker-compose.

We need to map a directory locally to iterate through creating a working import sql file, it makes it easier to develop sql on your host and make it visible in Docker.

So under the volumes in the psql service in my docker-compose.yml, I added an entry for import.

   
   cd cockroach-docker/cockroach-gssapi
mkdir import

   
  
 
   volumes:
      - ./kdc/krb5.conf:/etc/krb5.conf
      - ./psql/start.sh:/start.sh
      - certs-client:/certs
      - keytab:/keytab
      - ${PWD}/import:/import 
  

Run ./up.sh script to start the environment.

   
  
 
   ./up.sh
cockroach uses an image, skipping
Building roach-cert
Step 1/15 : FROM cockroachdb/cockroach:v20.1.3 AS generator
 ---> 25bee4f016c4
...
Creating roach-cert ... done
Creating kdc        ... done
Creating cockroach  ... done
Creating psql       ... done
CREATE ROLE

Time: 8.8429ms

GRANT

Time: 7.2032ms

SET CLUSTER SETTING

Time: 12.1494ms

SET CLUSTER SETTING

Time: 9.583ms

SET CLUSTER SETTING

Time: 8.3226ms

SET CLUSTER SETTING

Time: 8.1053ms 
  

4. Check to make sure all containers are up.

docker-compose ps

   
  
 
      Name                 Command               State                Ports
--------------------------------------------------------------------------------------
cockroach    /cockroach/cockroach.sh st ...   Up      0.0.0.0:26257->26257/tcp,
                                                      0.0.0.0:8080->8080/tcp
kdc          /start.sh                        Up
psql         /start.sh                        Up      5432/tcp
roach-cert   /bin/sh -c tail -f /dev/null     Up 
  

5. Create an import file in the import directory on your host.

It is easier to split your terminal window. On my host, I created a file called import.sql and placed it in my import directory.

   
  
 
   DROP TABLE IF EXISTS countries;

IMPORT
TABLE
    countries (
        id INT8 PRIMARY KEY, country STRING,
        INDEX country_idx (country)
    )
CSV
    DATA ('https://api.mockaroo.com/api/3b3df050?count=1000&key=02f7f490'); 
  

6. Validate this file is available in the psql container.

   
   docker exec -it psql bin/sh
# ls /import
import.sql

7. Add user tester adminprivileges to perform import.

We can stay in the psql instance or use the cockroach instance directly to do so. Keep in mind that I only have one admin user currently and it's root. The only way to login with root is by using certs.

docker exec -it psql bin/sh

   
   psql "postgresql://cockroach:26257?sslcert=/certs%2Fclient.root.crt&sslkey=/certs%2Fclient.root.key&sslmode=verify-full&sslrootcert=/certs%2Fca.crt"

   
   # psql "postgresql://cockroach:26257?sslcert=/certs%2Fclient.root.crt&sslkey=/certs%2Fclient.root.key&sslmode=verify-full&sslrootcert=/certs%2Fca.crt"
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.

root=# GRANT ADMIN TO tester;
GRANT
root=# \q

8. Make sure the user performing the import is authenticated with Kerberos.

   
  
 
   # kinit tester
Password for tester@EXAMPLE.COM:
# klist
Ticket cache: FILE:/tmp/krb5cc_0
Default principal: tester@EXAMPLE.COM

Valid starting       Expires              Service principal
07/24/2020 15:34:06  07/25/2020 15:34:06  krbtgt/EXAMPLE.COM@EXAMPLE.COM
    renew until 07/24/2020 15:34:06 
  

9. Perform the import as user tester

   
   psql "postgresql://cockroach:26257/defaultdb?sslmode=verify-full&sslrootcert=/certs/ca.crt" -U tester -f /import/import.sql

   
   DROP TABLE
       job_id       |  status   | fraction_completed | rows | index_entries | bytes
--------------------+-----------+--------------------+------+---------------+-------
 575191699644153857 | succeeded |                  1 | 1000 |          1000 | 41220
(1 row)

#

We can look at the data now.

   
   psql "postgresql://cockroach:26257/defaultdb?sslmode=verify-full&sslrootcert=/certs/ca.crt" -U tester

   
  
 
   defaultdb=> select count(*) from countries;
 count
-------
  1000
(1 row)

defaultdb=> select * from countries limit 5;
 id |   country
----+-------------
  1 | Philippines
  2 | Indonesia
  3 | Indonesia
  4 | Cape Verde
  5 | Ecuador
(5 rows)

defaultdb=> 
  

And that's that, we validated that user tester was able to perform an import over GSSAPI. Until next time!

Comments