Secure Connection from PrivacyIDEA to MYSQL PrivacyIDEA 3.1.2

After running mysql_ssl_rsa_setup to generate certificates and changing the mysql connection string in pi.cfg to include &ssl_key=/var/lib/mysql/client-key.pem&ssl_cert=/var/lib/mysql/client-cert.pem and restarting Apache2, I get an error when trying to access the web interface:

{"jsonrpc": "2.0", "signature": "rsa_sha256_pss:xxx", "detail": null, "version": "privacyIDEA 3.1.2", "result": {"status": false, "error": {"message": "[Errno 13] Permission denied", "code": -500}}, "time": 1573857886.340621, "id": 1}

What file is attempting to be accessed that gets a permission denied?

Here’s the full error from /var/log/apache2/error.log

[Fri Nov 15 16:47:39.352607 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680] [2019-11-15 16:47:39,350] ERROR in app: Exception on / [GET]
[Fri Nov 15 16:47:39.352682 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680] Traceback (most recent call last):
[Fri Nov 15 16:47:39.352695 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/flask/app.py", line 2292, in wsgi_app
[Fri Nov 15 16:47:39.352706 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     response = self.full_dispatch_request()
[Fri Nov 15 16:47:39.352716 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/flask/app.py", line 1815, in full_dispatch_request
[Fri Nov 15 16:47:39.352726 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     rv = self.handle_user_exception(e)
[Fri Nov 15 16:47:39.352736 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/flask/app.py", line 1718, in handle_user_exception
[Fri Nov 15 16:47:39.352746 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     reraise(exc_type, exc_value, tb)
[Fri Nov 15 16:47:39.352756 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/flask/app.py", line 1813, in full_dispatch_request
[Fri Nov 15 16:47:39.352810 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     rv = self.dispatch_request()
[Fri Nov 15 16:47:39.352823 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/flask/app.py", line 1799, in dispatch_request
[Fri Nov 15 16:47:39.352832 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     return self.view_functions[rule.endpoint](**req.view_args)
[Fri Nov 15 16:47:39.352842 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/privacyidea/webui/login.py", line 91, in single_page_application
[Fri Nov 15 16:47:39.352852 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     get_from_config(SYSCONF.OVERRIDECLIENT))
[Fri Nov 15 16:47:39.352861 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/privacyidea/lib/log.py", line 154, in log_wrapper
[Fri Nov 15 16:47:39.352870 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     return func(*args, **kwds)
[Fri Nov 15 16:47:39.352879 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/privacyidea/lib/config.py", line 337, in get_from_config
[Fri Nov 15 16:47:39.352888 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     config_object = get_config_object()
[Fri Nov 15 16:47:39.352897 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/privacyidea/lib/config.py", line 317, in get_config_object
[Fri Nov 15 16:47:39.352906 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     store['config_object'] = shared_config.reload_and_clone()
[Fri Nov 15 16:47:39.352915 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/privacyidea/lib/config.py", line 173, in reload_and_clone
[Fri Nov 15 16:47:39.352924 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     self._reload_from_db()
[Fri Nov 15 16:47:39.352933 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/privacyidea/lib/config.py", line 99, in _reload_from_db
[Fri Nov 15 16:47:39.352942 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     db_ts = Config.query.filter_by(Key=PRIVACYIDEA_TIMESTAMP).first()
[Fri Nov 15 16:47:39.352953 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3214, in first
[Fri Nov 15 16:47:39.352969 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     ret = list(self[0:1])
[Fri Nov 15 16:47:39.352987 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3006, in __getitem__
[Fri Nov 15 16:47:39.353006 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     return list(res)
[Fri Nov 15 16:47:39.353023 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3316, in __iter__
[Fri Nov 15 16:47:39.353037 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     return self._execute_and_instances(context)
[Fri Nov 15 16:47:39.353047 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3338, in _execute_and_instances
[Fri Nov 15 16:47:39.353056 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     querycontext, self._connection_from_session, close_with_result=True
[Fri Nov 15 16:47:39.353079 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3353, in _get_bind_args
[Fri Nov 15 16:47:39.353089 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     mapper=self._bind_mapper(), clause=querycontext.statement, **kw
[Fri Nov 15 16:47:39.353098 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3331, in _connection_from_session
[Fri Nov 15 16:47:39.353108 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     conn = self.session.connection(**kw)
[Fri Nov 15 16:47:39.353117 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1123, in connection
[Fri Nov 15 16:47:39.353126 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     execution_options=execution_options,
[Fri Nov 15 16:47:39.353135 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1129, in _connection_for_bind
[Fri Nov 15 16:47:39.353144 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     engine, execution_options
[Fri Nov 15 16:47:39.353153 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 430, in _connection_for_bind
[Fri Nov 15 16:47:39.353162 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     conn = bind._contextual_connect()
[Fri Nov 15 16:47:39.353171 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2226, in _contextual_connect
[Fri Nov 15 16:47:39.353180 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     self._wrap_pool_connect(self.pool.connect, None),
[Fri Nov 15 16:47:39.353189 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2262, in _wrap_pool_connect
[Fri Nov 15 16:47:39.353198 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     return fn()
[Fri Nov 15 16:47:39.353206 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/sqlalchemy/pool/base.py", line 354, in connect
[Fri Nov 15 16:47:39.353216 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     return _ConnectionFairy._checkout(self)
[Fri Nov 15 16:47:39.353224 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/sqlalchemy/pool/base.py", line 751, in _checkout
[Fri Nov 15 16:47:39.353234 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     fairy = _ConnectionRecord.checkout(pool)
[Fri Nov 15 16:47:39.353242 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/sqlalchemy/pool/base.py", line 483, in checkout
[Fri Nov 15 16:47:39.353252 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     rec = pool._do_get()
[Fri Nov 15 16:47:39.353260 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/sqlalchemy/pool/impl.py", line 138, in _do_get
[Fri Nov 15 16:47:39.353269 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     self._dec_overflow()
[Fri Nov 15 16:47:39.353308 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
[Fri Nov 15 16:47:39.353320 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     compat.reraise(exc_type, exc_value, exc_tb)
[Fri Nov 15 16:47:39.353329 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/sqlalchemy/pool/impl.py", line 135, in _do_get
[Fri Nov 15 16:47:39.353338 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     return self._create_connection()
[Fri Nov 15 16:47:39.353347 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/sqlalchemy/pool/base.py", line 299, in _create_connection
[Fri Nov 15 16:47:39.353356 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     return _ConnectionRecord(self)
[Fri Nov 15 16:47:39.353365 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/sqlalchemy/pool/base.py", line 428, in __init__
[Fri Nov 15 16:47:39.353374 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     self.__connect(first_connect_check=True)
[Fri Nov 15 16:47:39.353382 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/sqlalchemy/pool/base.py", line 630, in __connect
[Fri Nov 15 16:47:39.353391 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     connection = pool._invoke_creator(self)
[Fri Nov 15 16:47:39.353400 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py", line 114, in connect
[Fri Nov 15 16:47:39.353408 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     return dialect.connect(*cargs, **cparams)
[Fri Nov 15 16:47:39.353417 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 453, in connect
[Fri Nov 15 16:47:39.353426 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     return self.dbapi.connect(*cargs, **cparams)
[Fri Nov 15 16:47:39.353434 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/pymysql/__init__.py", line 90, in Connect
[Fri Nov 15 16:47:39.353443 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     return Connection(*args, **kwargs)
[Fri Nov 15 16:47:39.353451 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/pymysql/connections.py", line 646, in __init__
[Fri Nov 15 16:47:39.353460 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     self.ctx = self._create_ssl_ctx(ssl)
[Fri Nov 15 16:47:39.353469 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]   File "/opt/privacyidea/local/lib/python2.7/site-packages/pymysql/connections.py", line 716, in _create_ssl_ctx
[Fri Nov 15 16:47:39.353477 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680]     ctx.load_cert_chain(sslp['cert'], keyfile=sslp.get('key'))
[Fri Nov 15 16:47:39.353486 2019] [wsgi:error] [pid 4341:tid 140306197669632] [remote 192.168.1.1:7680] IOError: [Errno 13] Permission denied

There you have your answer at the end of the traceback in the log file!
It is probably the private key file, that you generated with the mysql_ssl_rsa_setup running as root - the MySQL documentation of the setup script also point out, that you need to adapt the access rights!

Note that most probably - I am just guessing, since your information is so sparse - your privacyidea job is not running as root!

I installed and am running everything using the admin account with elevated permissions (sudo su run prior to all configurations) that was setup during the OS install. Does PrivacyIDEA require being run under the root account or does the account I’m using not have the appropriate permissions? Is there a way I can identify which file is failing to open because of permissions?

Some more research…

With the latest version of MySQL that is installed with the PrivacyIDEA-Apache2 package, the mysql_ssl_rsa_setup script automatically runs. In fact, it automatically runs everytime the service is started. The script checks for the presence of certificates in the /var/lib/mysql folder. If the files are present it makes no changes and exits. If they are not present, new self-signed certs are generated under the uid, mysql.

I deleted all the .pem files in that folder rm /var/lib/mysql/*.pem, then ran mysql_ssl_rsa_setup --uid privacyidea. This created all new certs and set the owner of the files as privacyidea. However, going into MySQL to verify SSL is enabled, shows have_openssl and have_ssl is set to DISABLED. Changing the ownership of /var/lib/mysql/*.pem to mysql:mysql and restarting MySQL, turns SSL back on.

In previous versions of PrivacyIDEA, this worked perfectly fine so I am hesitant to say it’s a permission issue with these particular certificates. Is there any way I can find out which specific files PrivacyIDEA is trying to access when you load the web admin login page?

Stumbled onto what appears to be the solution…not sure if other stuff will be broken because of it…I’m thinking nothing will be.

Remove pymysql from the connection string

SQLALCHEMY_DATABASE_URI = 'mysql://pi:@localhost/pi?charset=utf8&ssl_key=/var/lib/mysql/client-key.pem&ssl_cert=/var/lib/mysql/client-cert.pem'

Setup a fresh VM, installed the privacyidea package, changed the connection string to the above, and everything started working.

I’m guessing something was changed in the python connector??