Import Data Into CockroachDB With Kerberos Authentication
A customer had asked how to leverage bulk utilities in CockroachDB while authenticated via Kerberos.
CORE
·
Join the DZone community and get the full member experience.
Join For FreeArticles 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-gssapidirectory 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.shscript 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.1053ms4. 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=# \q8. 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!
Published at DZone with permission of Artem Ervits. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments