InfoSec Architect 102-Service Accounts in the Linux Universe

# Update system packages
sudo apt update && sudo apt upgrade -y

# Install LDAP core services and tools
sudo apt install -y slapd ldap-utils

# Install Kerberos Key Distribution Center (KDC), admin server, and client tools
sudo apt install -y krb5-kdc krb5-admin-server krb5-user

sudo dpkg-reconfigure slapd

ss@ss:~$ ldapsearch -x -LLL -b "dc=supasaf,dc=com"
dn: dc=supasaf,dc=com
objectClass: top
objectClass: dcObject
objectClass: organization
o: Supasaf
dc: supasaf

sudo krb5_newrealm
# The system will ask you to enter and confirm the KDC master password, for example: master123

ss@ss:~$ sudo kadmin.local
Authenticating as principal root/[email protected] with password.
kadmin.local:  addprinc [email protected]
No policy specified for [email protected]; defaulting to no policy
Enter password for principal "[email protected]": # Enter a password
Re-enter password for principal "[email protected]": # Confirm it
Principal "[email protected]" created.
kadmin.local:  quit

# 1. Use kinit to ask the KDC for a TGT
ss@ss:~$ kinit [email protected]
Password for [email protected]: # Enter the password

# 2. Use klist to see the ticket we received
ss@ss:~$ klist
Ticket cache: FILE:/tmp/krb5cc_1000
Default principal: [email protected]

Valid starting         Expires                Service principal
10/02/2025 17:29:27  10/03/2025 03:29:27  krbtgt/[email protected]
        renew until 10/03/2025 17:29:23

# Create the users Organizational Unit (OU)
dn: ou=users,dc=supasaf,dc=com
objectClass: organizationalUnit
ou: users

# Create the groups OU
dn: ou=groups,dc=supasaf,dc=com
objectClass: organizationalUnit
ou: groups

# Create the services OU
dn: ou=services,dc=supasaf,dc=com
objectClass: organizationalUnit
ou: services

ss@ss:~/ldap1002$ ldapadd -x -D "cn=admin,dc=supasaf,dc=com" -W -f add-structure.ldif
Enter LDAP Password: # Enter the admin password you set earlier
adding new entry "ou=users,dc=supasaf,dc=com"
adding new entry "ou=groups,dc=supasaf,dc=com"
adding new entry "ou=services,dc=supasaf,dc=com"

dn: uid=alice,ou=users,dc=supasaf,dc=com
objectClass: inetOrgPerson
objectClass: posixAccount
objectClass: shadowAccount
uid: alice
cn: Alice Wang
sn: Wang
loginShell: /bin/bash
uidNumber: 10001
gidNumber: 10001
homeDirectory: /home/alice
userPassword: {SSHA}IoOlXeNSaWG0jxUZcJQib6yAF/dzchFd # This needs to be generated

dn: uid=bob,ou=users,dc=supasaf,dc=com
objectClass: inetOrgPerson
objectClass: posixAccount
objectClass: shadowAccount
uid: bob
cn: Bob Li
sn: Li
loginShell: /bin/bash
uidNumber: 10002
gidNumber: 10002
homeDirectory: /home/bob
userPassword: {SSHA}another_password_hash_here

ss@ss:~/ldap1002$ slappasswd
New password: # Enter a password
Re-enter new password: # Confirm it
{SSHA}IoOlXeNSaWG0jxUZcJQib6yAF/dzchFd # Copy this hash

ss@ss:~/ldap1002$ ldapadd -x -D "cn=admin,dc=supasaf,dc=com" -W -f add-users.ldif
Enter LDAP Password:
adding new entry "uid=alice,ou=users,dc=supasaf,dc=com"
adding new entry "uid=bob,ou=users,dc=supasaf,dc=com"

sudo apt install -y sasl2-bin libsasl2-modules-gssapi-mit

ss@ss:~/ldap1002$ sudo kadmin.local
Authenticating as principal root/[email protected] with password.
# -randkey generates a random key, which is much more secure than a password
kadmin.local:  addprinc -randkey ldap/[email protected]
Principal "ldap/[email protected]" created.

# Export this principal's key into a keytab file
kadmin.local:  ktadd -k /etc/ldap/ldap.keytab ldap/[email protected]
Entry for principal ldap/[email protected] with kvno 2, ... added to keytab WRFILE:/etc/ldap/ldap.keytab.
...
kadmin.local:  quit

# This keytab file is super sensitive. Lock it down!
ss@ss:~/ldap1002$ sudo chown openldap:openldap /etc/ldap/ldap.keytab
ss@ss:~/ldap1002$ sudo chmod 400 /etc/ldap/ldap.keytab

# Edit the file
sudo vim /etc/default/saslauthd

# Change the content to this:
START=yes
MECHANISMS="kerberos5"

# Restart the services to apply our changes
ss@ss:~/ldap1002$ sudo systemctl restart saslauthd
ss@ss:~/ldap1002$ sudo systemctl restart slapd

ss@ss:~/ldap1002$ sudo kadmin.local
Authenticating as principal root/[email protected] with password.
# Create principals for HDFS and YARN services
kadmin.local:  addprinc -randkey hdfs/[email protected]
Principal "hdfs/[email protected]" created.
kadmin.local:  addprinc -randkey yarn/[email protected]
Principal "yarn/[email protected]" created.

# Create a principal for a business service
kadmin.local:  addprinc -randkey [email protected]
Principal "[email protected]" created.

# Generate keytabs for our hdfs and uber_eats services
kadmin.local:  ktadd -k /tmp/hdfs.keytab hdfs/[email protected]
...
kadmin.local:  ktadd -k /tmp/uber_eats.keytab [email protected]
...
kadmin.local:  quit

# Make sure the service process could read it
ss@ss:~/ldap1002$ sudo chmod 644 /tmp/hdfs.keytab

# Authenticate using the hdfs.keytab file
ss@ss:~/ldap1002$ kinit -kt /tmp/hdfs.keytab hdfs/[email protected]

# Immediately check our ticket cache
ss@ss:~/ldap1002$ klist
Ticket cache: FILE:/tmp/krb5cc_1000
Default principal: hdfs/[email protected]

Valid starting         Expires                Service principal
10/02/2025 22:07:38  10/03/2025 08:07:38  krbtgt/[email protected]
        renew until 10/03/2025 22:07:38

ss@ss:~$ sudo apt install -y postgresql postgresql-contrib libpq-dev

ss@ss:~$ pg_config | grep -i gssapi
CONFIGURE =  '--build=x86_64-linux-gnu' ... '--with-gssapi' ...
LIBS = ... -lgssapi_krb5 ...

# Log in to the kadmin tool
sudo kadmin.local

# Create a principal for the PostgreSQL service.
# The naming convention is usually: service_name/hostname@REALM
# The hostname 'ss' MUST match what the client uses to connect.
kadmin.local> addprinc -randkey postgres/[email protected]

# Export its key to a keytab file
kadmin.local> ktadd -k /etc/postgresql/postgres.keytab postgres/[email protected]

kadmin.local> quit

# Set the correct permissions so only the postgres user can read this secret file
sudo chown postgres:postgres /etc/postgresql/postgres.keytab
sudo chmod 400 /etc/postgresql/postgres.keytab

sudo kadmin.local

# Create a principal for our application service
kadmin.local> addprinc -randkey [email protected]

# Export its keytab, this time to our home directory
kadmin.local> ktadd -k /home/ss/data_service.keytab [email protected]

kadmin.local> quit

# Set permissions so the user running the app (ss) can read it
sudo chown ss:ss /home/ss/data_service.keytab
chmod 400 /home/ss/data_service.keytab

sudo vim /etc/postgresql/16/main/postgresql.conf

# Add or modify these lines at the end of the file
# Kerberos configuration - point to the service's own keytab file
krb_server_keyfile = '/etc/postgresql/postgres.keytab'

# Make sure the database is listening for network connections
listen_addresses = '*'

sudo vim /etc/postgresql/16/main/pg_hba.conf

# Add this rule at the very top of the file to give it priority
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            gss include_realm=0 krb_realm=SUPASAF.COM
host    all             all             ::1/128                 gss include_realm=0 krb_realm=SUPASAF.COM

sudo systemctl restart postgresql

# Switch to the postgres system user and open the psql command line
ss@ss:~$ sudo -u postgres psql
psql (16.10 (Ubuntu 16.10-0ubuntu0.24.04.1))
Type "help" for help.

-- Create a database user corresponding to the Kerberos principal 'data_service'
postgres=# CREATE USER data_service;
CREATE ROLE

-- Create a test database and give all permissions to our new user
postgres=# CREATE DATABASE testdb;
CREATE DATABASE
postgres=# GRANT ALL PRIVILEGES ON DATABASE testdb TO data_service;
GRANT

-- Connect to the new database
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".

-- Create a test table and grant permissions
testdb=# CREATE TABLE kerberos_test (
    id SERIAL PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE
testdb=# GRANT ALL PRIVILEGES ON TABLE kerberos_test TO data_service;
GRANT
testdb=# GRANT USAGE, SELECT ON SEQUENCE kerberos_test_id_seq TO data_service;
GRANT

-- Exit psql
testdb=# \q

ss@ss:~$ sudo apt install -y libkrb5-dev krb5-user
ss@ss:~$ pip3 install psycopg2-binary gssapi --break-system-packages

import os
import subprocess
import psycopg2
from datetime import datetime


class KerberosDBConnection:
    """A database connection manager that uses Kerberos authentication."""

    def __init__(self, keytab_path, principal, db_host, db_name, db_port=5432):
        """
        Initializes the Kerberos database connection.

        Args:
            keytab_path: Full path to the keytab file.
            principal: The Kerberos principal (e.g., [email protected]).
            db_host: Database hostname.
            db_name: Database name.
            db_port: Database port.
        """
        self.keytab_path = keytab_path
        self.principal = principal
        self.db_host = db_host
        self.db_name = db_name
        self.db_port = db_port
        self.conn = None

    def authenticate(self):
        """Perform Kerberos authentication using the keytab."""
        try:
            # Destroy any existing tickets
            subprocess.run(['kdestroy'], stderr=subprocess.DEVNULL)

            # Get a TGT using the keytab
            cmd = ['kinit', '-kt', self.keytab_path, self.principal]
            result = subprocess.run(cmd, capture_output=True, text=True)

            if result.returncode != 0:
                raise Exception(f"Kerberos authentication failed: {result.stderr}")

            print(f"✓ Kerberos authentication successful for: {self.principal}")

            # Show ticket info
            klist_result = subprocess.run(['klist'], capture_output=True, text=True)
            print("\nCurrent Kerberos Tickets:")
            print(klist_result.stdout)

            return True

        except Exception as e:
            print(f"✗ Kerberos authentication failed: {e}")
            return False

    def connect(self):
        """Establish the database connection (using GSSAPI)."""
        try:
            # Extract username from the principal
            db_user = self.principal.split('@')[0]

            # Connection string (key part: use gssencmode=prefer)
            connection_params = {
                'host': self.db_host,
                'port': self.db_port,
                'dbname': self.db_name,
                'user': db_user,
                'gssencmode': 'prefer',  # Use GSSAPI encryption
                'sslmode': 'prefer'
            }

            print(f"\nAttempting to connect to the database...")
            print(f"  Host: {self.db_host}:{self.db_port}")
            print(f"  Database: {self.db_name}")
            print(f"  User: {db_user}")
            print(f"  Auth Method: Kerberos GSSAPI (Password-Free)")

            self.conn = psycopg2.connect(**connection_params)
            print("✓ Database connection successful!")

            return True

        except Exception as e:
            print(f"✗ Database connection failed: {e}")
            return False

    def execute_query(self, query, params=None, fetch=True):
        """Execute a SQL query."""
        if not self.conn:
            raise Exception("Database is not connected")

        try:
            cursor = self.conn.cursor()
            cursor.execute(query, params)

            # For SELECT or RETURNING clauses, fetch results
            if fetch or query.strip().upper().startswith('SELECT') or 'RETURNING' in query.upper():
                result = cursor.fetchall()
                self.conn.commit()
                return result
            else:
                self.conn.commit()
                return cursor.rowcount

        except Exception as e:
            self.conn.rollback()
            raise e

    def close(self):
        """Close the connection."""
        if self.conn:
            self.conn.close()
            print("\nDatabase connection closed.")


def main():
    """Main function to demonstrate passwordless Kerberos database access."""

    print("=" * 60)
    print("Kerberos Passwordless Database Access Demo")
    print("=" * 60)

    # Configuration (change according to your environment)
    KEYTAB_PATH = "/home/ss/data_service.keytab"
    PRINCIPAL = "[email protected]"
    DB_HOST = "ss"  # Or use FQDN
    DB_NAME = "testdb"

    # Create the connection manager
    db = KerberosDBConnection(
        keytab_path=KEYTAB_PATH,
        principal=PRINCIPAL,
        db_host=DB_HOST,
        db_name=DB_NAME
    )

    try:
        # Step 1: Kerberos Authentication
        if not db.authenticate():
            return

        # Step 2: Connect to the Database
        if not db.connect():
            return

        # Step 3: Insert test data
        print("\n" + "=" * 60)
        print("Inserting Test Data")
        print("=" * 60)

        # Insert a test record with a timestamp
        insert_query = """
            INSERT INTO kerberos_test (message) 
            VALUES (%s) 
            RETURNING id, message, created_at
        """
        test_message = f"Passwordless Kerberos auth test - {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}"

        print(f"\nInserting message: {test_message}")
        result = db.execute_query(insert_query, (test_message,))

        if result:
            record_id = result[0][0]
            record_msg = result[0][1]
            record_time = result[0][2]

            print(f"\n✓ Data inserted successfully!")
            print(f"  Record ID: {record_id}")
            print(f"  Message: {record_msg}")
            print(f"  Timestamp: {record_time}")

            print("\n" + "=" * 60)
            print("Verification Steps")
            print("=" * 60)
            print("\nYou can now verify the data was inserted with these commands:")
            print("\n1. Log into the database as the postgres user:")
            print("   sudo -u postgres psql -d testdb")
            print("\n2. Run a query inside psql:")
            print("   SELECT * FROM kerberos_test ORDER BY id DESC LIMIT 5;")
            print("\n3. Or run directly from the command line:")
            print(f"   sudo -u postgres psql -d testdb -c \"SELECT * FROM kerberos_test WHERE id = {record_id};\"")
            print("\n4. Exit psql:")
            print("   \\q")

        print("\n✓ All operations completed! No database password was used.")

    except Exception as e:
        print(f"\n✗ An error occurred: {e}")
        import traceback
        traceback.print_exc()

    finally:
        db.close()


if __name__ == "__main__":
    main()

# Make sure the keytab file exists and has the right permissions
ss@ss:~/ldap1002$ ls -l /home/ss/data_service.keytab
-r-------- 1 ss ss 150 Oct  4 10:22 /home/ss/data_service.keytab

# Run our Python app
ss@ss:~/ldap1002$ python3 kerberos_db_access.py
============================================================
Kerberos Passwordless Database Access Demo
============================================================
✓ Kerberos authentication successful for: [email protected]

Current Kerberos Tickets:
Ticket cache: FILE:/tmp/krb5cc_1000
Default principal: [email protected]

Valid starting         Expires                Service principal
10/04/2025 10:55:42  10/04/2025 20:55:42  krbtgt/[email protected]
        renew until 10/05/2025 10:55:42


Attempting to connect to the database...
  Host: ss:5432
  Database: testdb
  User: data_service
  Auth Method: Kerberos GSSAPI (Password-Free)
✓ Database connection successful!

============================================================
Inserting Test Data
============================================================

Inserting message: Passwordless Kerberos auth test - 2025-10-04 10:55:42

✓ Data inserted successfully!
  Record ID: 2
  Message: Passwordless Kerberos auth test - 2025-10-04 10:55:42
  Timestamp: 2025-10-04 10:55:42.331621

============================================================
Verification Steps
============================================================

You can now verify the data was inserted with these commands:

1. Log into the database as the postgres user:
   sudo -u postgres psql -d testdb

2. Run a query inside psql:
   SELECT * FROM kerberos_test ORDER BY id DESC LIMIT 5;

3. Or run directly from the command line:
   sudo -u postgres psql -d testdb -c "SELECT * FROM kerberos_test WHERE id = 2;"

4. Exit psql:
   \q

✓ All operations completed! No database password was used.

Database connection closed.

ss@ss:~$ sudo -u postgres psql -d testdb
[sudo] password for ss:
psql (16.10 (Ubuntu 16.10-0ubuntu0.24.04.1))
Type "help" for help.

testdb=# SELECT * FROM kerberos_test ORDER BY id DESC LIMIT 5;
 id |                   message                     |          created_at
----+-----------------------------------------------+----------------------------
  2 | Passwordless Kerberos auth test - 2025-10-04 10:55:42 | 2025-10-04 10:55:42.331621
  1 | Passwordless Kerberos auth test - 2025-10-04 10:54:02 | 2025-10-04 10:54:02.399912
(2 rows)

testdb=# \q