Kerberize MariaDB and enable SQLAlchemy to talk to MariaDB via python(On CentOS 7.0)

If you already have FreeIPA  and RDO  up and running, you may know that MariaDB is used as database for RDO and it is not Kerberized, which means openstack components just talk to MariaDB directly without Kerberos authentication. Definitely it’s not secure. In this article, I’m going to discuss how to build Kerberized mariaDB for RDO and enable SQLAlchemy to communicate to it via python.

We have discussed building FreeIPA server before and building RDO is much easier than that if you use packstack. I suggest you to read this to build your RDO: https://www.rdoproject.org/Quickstart .If you run into a lot of troubles building the RDO, simply switch to CentOS 7.0. You may also find lack of dependency by running “packstack –allinone”, you can “yum install” with “–nodeps” option to install the missing dependency and it should be good to go.

Now it comes to MariaDB. My colleague, Robbie, has update the Kerberized MariaDB in his repo. All we need to do is pull it out and configure it. Before doing this, let’s say we have IPA server as ipa.charlie.com and a new VM with hostname as maria.charlie.com(you can change it in /etc/hostname file), make sure these can be resolved(nslookup can help you to test it).

First, install ipa-admintools and configure your machine as an ipa-client, make sure ipa.charlie.com can be resolved. If not, edit /etc/resolv.conf, change the first nameserver as your ipa server’s address.

$ sudo su -
$ yum install ipa-client ipa-admintools
$ ipa-client-install --force-ntpd
WARNING: ntpd time&date synchronization service will not be configured as
conflicting service (chronyd) is enabled
Use --force-ntpd option to disable it and force configuration of ntpd
Discovery was successful!
Hostname: maria.charlie.com
Realm: CHARLIE.COM
DNS Domain: charlie.com
IPA Server: ipa.charlie.com
BaseDN: dc=charlie,dc=com

Continue to configure the system with these values? [no]: yes
Synchronizing time with KDC...
Unable to sync time with IPA NTP server, assuming the time is in sync. Please check that 123 UDP port is opened.
User authorized to enroll computers: admin
Password for admin@CHARLIE.COM: 
.
.
.
Client configuration complete.


Second, update the repo of Robbie to download the MariaDB of his version.

$ cd /etc/yum.repos.d/
$ wget https://copr.fedoraproject.org/coprs/rharwood/mariadb/repo/epel-7/rharwood-mariadb-epel-7.repo
$ yum install epel-release
$ yum update
$ yum install mariadb{,-debuginfo,-devel,-libs,-server}
$ ipa service-add MySQL/$(hostname -f)

Third, configure your MariaDB(All corresponding hostname and domain name need to be adjusted to your previous setting).

$ cd /var/lib/mysql
$ ipa-getkeytab -s ipa.charlie.com -p MySQL/$(hostname -f)@CHARLIE.COM -k mysql.keytab
$ chown mysql:mysql mysql.keytab
$ chmod 660 /var/lib/mysql/mysql.keytab
$ kinit admin   #your username may be different from mine according to your previous setting
$ service mariadb start
$ mysql -u root    #now we go into mysql CLI
MariaDB [(none)]> install plugin kerberos soname 'kerberos';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> \q    #to quit mariaDB
Bye
$ service mariadb stop
$ vi /etc/my.cnf.d/server.cnf 
#edit [server] section like this
# this is read by the standalone daemon and embedded servers
[server]
kerberos_principal_name=MySQL/maria.charlie.com@CHARLIE.COM
kerberos_keytab_path=/var/lib/mysql/mysql.keytab
$ service mariadb start 
$ mysql -u root (enter mysql CLI)
MariaDB [(none)]> select @@kerberos_principal_name;
+-------------------------------------+       #This is what you should see
| @@kerberos_principal_name |
+-------------------------------------+
| MySQL/maria.charlie.com@CHARLIE.COM |
+-------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> select @@kerberos_keytab_path;
+-----------------------------+
| @@kerberos_keytab_path |
+-----------------------------+
| /var/lib/mysql/mysql.keytab |
+-----------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> CREATE USER test_kerberos IDENTIFIED VIA kerberos AS 'admin@CHARLIE.COM';
MariaDB [(none)]> \q
Bye

If everything goes fine, you are good to go. To test it, you can do this:

$ kdestroy -A
$ kinit admin@CHARLIE.COM
$ mysql  -u  test_kerberos   -h maria.charlie.com
MariaDB [(none)]> select user();
+------------------------------+          #you should see following info if you are all set
| user() |
+------------------------------+
| test_kerberos@192.168.122.26 |
+------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> \q
Bye
[root@maria mysql]# klist
Ticket cache: KEYRING:persistent:0:0
Default principal: admin@CHARLIE.COM

Valid starting Expires Service principal
06/10/2015 14:18:22 06/11/2015 14:18:04 MySQL/maria.charlie.com@CHARLIE.COM
06/10/2015 14:18:06 06/11/2015 14:18:04 krbtgt/CHARLIE.COM@CHARLIE.COM

Now, let’s go one step further and try to use SQLAlchemy to talk to MariaDB. SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.  To enable this functionality, try the following scripts:

$ yum install python-devel
$ virtualenv test
$ . test/bin/activate
(test)[root@maria mysql]# pip install sqlalchemy
(test)[root@maria mysql]# yum install python-sqlalchemy
(test)[root@maria mysql]# pip install MySQL-python
(test)[root@maria mysql]# vi engine_test.py       #build your first python-alchemy script

Here is an example of your engine_test.py script:

#!/usr/bin/python
from sqlalchemy.engine import create_engine
engine = create_engine('mysql://test_kerberos@maria.charlie.com/test')
#engine = create_engine('mysql://charlie:charlie@127.0.0.1/test')
connection = engine.connect()
connection.execute(
    """
   DROP TABLE test;
   CREATE TABLE test (id int );
    """
)
connection.execute(
    "INSERT INTO test (id) VALUES (4);"
)
result = connection.execute("SELECT * FROM test;")
for row in result:
    print "id:", row['id']
connection.close()

Run the script:

(test)[root@maria ~]# python engine_test.py 
id: 4
(test)[root@maria mysql]# klist
(test4)[root@maria ~]# klist
Ticket cache: KEYRING:persistent:0:0
Default principal: admin@CHARLIE.COM

Valid starting       Expires              Service principal
06/10/2015 15:09:32  06/11/2015 14:52:57  MySQL/maria.charlie.com@CHARLIE.COM
06/10/2015 14:52:59  06/11/2015 14:52:57  krbtgt/CHARLIE.COM@CHARLIE.COM

Works as a charm, Yeah.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s