DZone
Data Engineering
Data
Import Data Into CockroachDB With Kerberos Authentication

Import Data Into CockroachDB With Kerberos Authentication

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

👁 Artem Ervits user avatar

Artem Ervits

👁 DZone Core
CORE ·

Jan. 26, 22 · Tutorial

Likes (3)

Comment

Save

5.3K Views

Join the DZone community and get the full member experience.

Join For Free

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 [email protected]:
# klist
Ticket cache: FILE:/tmp/krb5cc_0
Default principal: [email protected]

Valid starting Expires Service principal
07/24/2020 15:34:06 07/25/2020 15:34:06 krbtgt/[email protected]
    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!

Kerberos (protocol) CockroachDB Data (computing) authentication

Published at DZone with permission of Artem Ervits. See the original article here.

Opinions expressed by DZone contributors are their own.

Set Up Spring Data Elasticsearch With Basic Authentication
API and Security: From IT to Cyber
The Impact of Biometric Authentication on User Privacy and the Role of Blockchain in Preserving Secure Data
The Role of Zero-Knowledge Proofs in LLM Chains for Data Privacy

URL: https://dzone.com/articles/import-data-into-cockroachdb-with-kerberos-authent