Bug 40857 - Provide database integration for Docker Apps
Provide database integration for Docker Apps
Status: CLOSED FIXED
Product: UCS
Classification: Unclassified
Component: App Center
UCS 4.1
Other Linux
: P2 enhancement (vote)
: UCS 4.1-3-errata
Assigned To: Dirk Wiesenthal
Felix Botner
:
Depends on: 39471 42117 42307
Blocks: 42061 42244
  Show dependency treegraph
 
Reported: 2016-03-08 13:00 CET by Dirk Wiesenthal
Modified: 2016-09-07 18:41 CEST (History)
2 users (show)

See Also:
What kind of report is it?: ---
What type of bug is this?: ---
Who will be affected by this bug?: ---
How will those affected feel about the bug?: ---
User Pain:
Enterprise Customer affected?:
School Customer affected?:
ISV affected?:
Waiting Support:
Flags outvoted (downgraded) after PO Review:
Ticket number:
Bug group (optional):
Max CVSS v3 score:


Attachments
appcenter.log.gz (48.59 KB, application/gzip)
2016-09-07 11:19 CEST, Felix Botner
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Dirk Wiesenthal univentionstaff 2016-03-08 13:00:53 CET
Docker Apps should be able to specify an external database where they want to store their data.

This database should run on the Docker Host via mysql or univention-postgres.
Comment 1 Stefan Gohmann univentionstaff 2016-07-14 07:52:05 CEST
Let's try to focus to PostgreSQL in a first step.

Does it work? What are the blockers?
Comment 2 Nico Gulden univentionstaff 2016-07-21 10:17:36 CEST
FYI: The app providers I have in mind for this feature need MySQL.
Comment 3 Dirk Wiesenthal univentionstaff 2016-08-11 01:39:16 CEST
ini:

Database=postgresql
or
Database=mysql

The code for creating databases and users looks ugly and seems to be prone to SQL injection (although this would come from the ini file...). Please double check.

Passwords are stored on the Host in /etc/mysql-$appid.secret and passed to the container as an envvariable.

DB_HOST is set to "$fqdn:$port". Don't know if this is very clever. Maybe one needs a dedicated DB_PORT?

For MySQL, the firewall needs to be opened. I have added a warning for those cases where MySQL lived behind a firewall before the first installation. I just realized that I should have used $(ucr get mysql/config/client/port). Reopen if this is a blocker.

From the inline doc:
database: Which (if any) database an App wants to use. The App
	Center will setup the database for the App. Useful for
	Docker Apps running against the Host's database.
database_name: Name of the database to be created. Defaults to
	"id" (- is replaced by _). Needs to be unique
database_user: Name of the database user to be created.
	Defaults to "id" (- is replaced by _). May not be
	"root" or "postgres".
docker_env_database_host: Environment variable name for the DB
	host.
docker_env_database_name: Environment variable name for the DB
	name.
docker_env_database_user: Environment variable name for the DB
	user.
docker_env_database_password: Environment variable name for the
	DB password (of "docker_env_database_user").


Fixed in
  univention-appcenter 5.0.22-1.207.201608110123
Comment 4 Felix Botner univentionstaff 2016-08-17 16:23:35 CEST
* check if database attribute is valid before installing the app

# database=sdsa
-> univention-app install dudle
Going to install Dudle (1.1.0-1)
(shall_not_have_mysql_filtered) The application will open MySQL ports in the firewall.
Do you want to install anyway [y/N]? y
Creating data directories for dudle...
Registering UCR for dudle
Marking dudle=1.1.0-1 as installed
File: /etc/univention/service.info/services/univention-appcenter.cfg
Multifile: /etc/postgresql/9.1/main/pg_hba.conf
Setting ports for apache proxy
Multifile: /etc/apache2/sites-available/default-ssl
Multifile: /etc/apache2/sites-available/default
Creating /etc/init.d/docker-app-dudle
 Adding system startup for /etc/init.d/docker-app-dudle ...
   /etc/rc0.d/K14docker-app-dudle -> ../init.d/docker-app-dudle
   /etc/rc1.d/K14docker-app-dudle -> ../init.d/docker-app-dudle
   /etc/rc6.d/K14docker-app-dudle -> ../init.d/docker-app-dudle
   /etc/rc2.d/S41docker-app-dudle -> ../init.d/docker-app-dudle
   /etc/rc3.d/S41docker-app-dudle -> ../init.d/docker-app-dudle
   /etc/rc4.d/S41docker-app-dudle -> ../init.d/docker-app-dudle
   /etc/rc5.d/S41docker-app-dudle -> ../init.d/docker-app-dudle
Adding localhost to LDAP object
Setting overview variables
File: /var/www/ucs-overview/entries.json
Reloading web server config: apache2.
Going to remove Dudle (1.1.0-1)
No hostdn for dudle found. Nothing to remove
 Removing any system startup links for /etc/init.d/docker-app-dudle ...
   /etc/rc0.d/K14docker-app-dudle
   /etc/rc1.d/K14docker-app-dudle
   /etc/rc2.d/S41docker-app-dudle
   /etc/rc3.d/S41docker-app-dudle
   /etc/rc4.d/S41docker-app-dudle
   /etc/rc5.d/S41docker-app-dudle
   /etc/rc6.d/K14docker-app-dudle
Removing localhost from LDAP object
File: /etc/univention/service.info/services/univention-appcenter.cfg
Multifile: /etc/postgresql/9.1/main/pg_hba.conf
Multifile: /etc/apache2/sites-available/default
File: /var/www/ucs-overview/entries.json
Multifile: /etc/apache2/sites-available/default-ssl
Reloading web server config: apache2.
Installing join script /var/cache/univention-appcenter/dudle_20160201.uinst
univention-run-join-scripts: runs all join scripts existing on local computer.
copyright (c) 2001-2016 Univention GmbH, Germany

Running 01univention-ldap-server-init.inst skipped (already executed)
Running 02univention-directory-notifier.inst skipped (already executed)
Running 03univention-directory-listener.inst skipped (already executed)
Running 04univention-ldap-client.inst skipped (already executed)
Running 05univention-bind.inst skipped (already executed)
Running 08univention-apache.inst skipped (already executed)
Running 10univention-ldap-server.inst skipped (already executed)
Running 11univention-heimdal-init.inst skipped (already executed)
Running 11univention-pam.inst skipped (already executed)
Running 15univention-directory-notifier-post.inst skipped (already executed)
Running 15univention-heimdal-kdc.inst skipped (already executed)
Running 18python-univention-directory-manager.inst skipped (already executed)
Running 20univention-directory-policy.inst skipped (already executed)
Running 20univention-join.inst skipped (already executed)
Running 26univention-nagios-common.inst skipped (already executed)
Running 30univention-appcenter.inst skipped (already executed)
Running 30univention-nagios-client.inst skipped (already executed)
Running 31univention-nagios-s4-connector.inst skipped (already executed)
Running 34univention-management-console-server.inst skipped (already executed)
Running 35univention-appcenter-docker.inst skipped (already executed)
Running 35univention-management-console-module-appcenter.inst skipped (already executed)
Running 35univention-management-console-module-diagnostic.inst skipped (already executed)
Running 35univention-management-console-module-ipchange.inst skipped (already executed)
Running 35univention-management-console-module-join.inst skipped (already executed)
Running 35univention-management-console-module-lib.inst skipped (already executed)
Running 35univention-management-console-module-mrtg.inst skipped (already executed)
Running 35univention-management-console-module-passwordchange.inst skipped (already executed)
Running 35univention-management-console-module-quota.inst skipped (already executed)
Running 35univention-management-console-module-reboot.inst skipped (already executed)
Running 35univention-management-console-module-services.inst skipped (already executed)
Running 35univention-management-console-module-setup.inst skipped (already executed)
Running 35univention-management-console-module-sysinfo.inst skipped (already executed)
Running 35univention-management-console-module-top.inst skipped (already executed)
Running 35univention-management-console-module-ucr.inst skipped (already executed)
Running 35univention-management-console-module-udm.inst skipped (already executed)
Running 35univention-management-console-module-updater.inst skipped (already executed)
Running 36univention-management-console-module-apps.inst skipped (already executed)
Running 40univention-postgresql.inst skipped (already executed)
Running 40univention-virtual-machine-manager-schema.inst skipped (already executed)
Running 51kix2016.inst skipped (already executed)
Running 81univention-nfs-server.inst skipped (already executed)
Running 90univention-bind-post.inst skipped (already executed)
Running 91univention-saml.inst skipped (already executed)
Running 92univention-management-console-web-server.inst skipped (already executed)
Running 94univention-openvpn-master.inst skipped (already executed)
Running 94univention-openvpn-server.inst skipped (already executed)
Running 94univention-openvpn-sitetosite.inst skipped (already executed)
Running 96univention-samba4.inst skipped (already executed)
Running 97univention-s4-connector.inst skipped (already executed)
Running 98univention-pkgdb-tools.inst skipped (already executed)
Running 98univention-samba4-dns.inst skipped (already executed)
Running 51dudle-uninstall.uinst done
Error sending app infos to the App Center server: HTTP Error 404: Not Found
dudle=1.1.0-1 wants u'sdsa' as database. This is unsupported!
Traceback (most recent call last):
  File "/usr/lib/pymodules/python2.7/univention/appcenter/actions/__init__.py", line 189, in call_with_namespace
    result = self.main(namespace)
  File "/usr/lib/pymodules/python2.7/univention/appcenter/actions/install.py", line 66, in main
    return self.do_it(args)
  File "/usr/lib/pymodules/python2.7/univention/appcenter/actions/install_base.py", line 107, in do_it
    self._do_it(app, args)
  File "/usr/lib/pymodules/python2.7/univention/appcenter/actions/docker_install.py", line 64, in _do_it
    ret = super(Install, self)._do_it(app, args)
  File "/usr/lib/pymodules/python2.7/univention/appcenter/actions/install.py", line 79, in _do_it
    self._register_database(app)
  File "/usr/lib/pymodules/python2.7/univention/appcenter/actions/register.py", line 270, in _register_database
    database_connector = DatabaseConnector.get_connector(app)
  File "/usr/lib/pymodules/python2.7/univention/appcenter/database.py", line 129, in get_connector
    raise DatabaseInfoError('%s wants %r as database. This is unsupported!' % (app, value))
DatabaseInfoError: dudle=1.1.0-1 wants u'sdsa' as database. This is unsupported!
Traceback (most recent call last):
  File "/usr/bin/univention-app", line 90, in <module>
    main()
  File "/usr/bin/univention-app", line 78, in main
    ret = args.func(args)
  File "/usr/lib/pymodules/python2.7/univention/appcenter/actions/__init__.py", line 189, in call_with_namespace
    result = self.main(namespace)
  File "/usr/lib/pymodules/python2.7/univention/appcenter/actions/install.py", line 66, in main
    return self.do_it(args)
  File "/usr/lib/pymodules/python2.7/univention/appcenter/actions/install_base.py", line 107, in do_it
    self._do_it(app, args)
  File "/usr/lib/pymodules/python2.7/univention/appcenter/actions/docker_install.py", line 64, in _do_it
    ret = super(Install, self)._do_it(app, args)
  File "/usr/lib/pymodules/python2.7/univention/appcenter/actions/install.py", line 79, in _do_it
    self._register_database(app)
  File "/usr/lib/pymodules/python2.7/univention/appcenter/actions/register.py", line 270, in _register_database
    database_connector = DatabaseConnector.get_connector(app)
  File "/usr/lib/pymodules/python2.7/univention/appcenter/database.py", line 129, in get_connector
    raise DatabaseInfoError('%s wants %r as database. This is unsupported!' % (app, value))
univention.appcenter.database.DatabaseInfoError: dudle=1.1.0-1 wants u'sdsa' as database. This is unsupported!
Comment 5 Felix Botner univentionstaff 2016-08-17 16:27:05 CEST
* i get this mysql firewall warning even if database is set to postgresql

# database=postgresql
-> univention-app install dudle
Going to install Dudle (1.1.0-1)
(shall_not_have_mysql_filtered) The application will open MySQL ports in the firewall.
Do you want to install anyway [y/N]? y
Comment 6 Dirk Wiesenthal univentionstaff 2016-08-18 00:26:02 CEST
I have fixed the warning message.

Checking Database value before installation has not been done. Instead, the traceback should be gone. The installation aborts before any Docker Image is used. One could do it one or two steps earlier, but is it really important? Also, what if those steps that currently come first would abort? One could argue that whatever causes the problem there should be checked first.

There would be the option to say
  database = AppAttribute(choices=[...])

But this has the disadvantage that in this case new database types would cause the whole App to be not listed (removed from the list of Apps).

This may be a problem when a new DB type is added, like CouchDB.

One could add a UMC warning if you like. Although I guess that this error will not come up very often.
Comment 7 Felix Botner univentionstaff 2016-08-18 13:56:44 CEST
(In reply to Dirk Wiesenthal from comment #6)
> I have fixed the warning message.
> 
> Checking Database value before installation has not been done. Instead, the
> traceback should be gone. The installation aborts before any Docker Image is
> used. One could do it one or two steps earlier, but is it really important?

no, it is ok as it is now

> shall_not_have_mysql_filtered) The application will open MySQL ports in the 
> firewall.
> Do you want to install anyway [y/N]? y

* actually, i think we should remove this test completely, why bother the user 
  with such questions?

* if self.database == 'mysql' and ucr_get('security/packetfilter/package/mysql/tcp/3306/all') !

i would prefer to handle this in univention-mysql (as it is done in univention-postgres), please do not start handling firewall exceptions in the appcenter

an alternative to opening the database service ports for everybody and/or handling firewall exceptions in the packages/appcenter code might be 

iptables -A INPUT -i docker0 -j ACCEPT

in /etc/security/packetfilter.d/20_docker.sh (univention-firewall).
Downside, all host ports are open for all docker containers

> DB_HOST is set to "$fqdn:$port". Don't know if this is very clever. Maybe one 
> needs a dedicated DB_PORT?

This is not good, i think we need a dedicated variables for host and port, most application configs have separate options for the db host and port ...

* DatabaseConnector.install()

-> add apt-get update before install

* mysql i got with a mysql docker app 

Starting MySQL database server: mysqld already running.
(1054, "Unknown column 'dudle' in 'where clause'")
Aborting...
seem that something is wrong in MySQL.db_user_exists


* installed my postgresql app on a slave via the master's UMC, worked, but 
  i got a "Confirmation" dialog box:

 Confirmation
11 errors occurred:

    * find: "/etc/postgresql/7.4/main/pg_hba.conf": Datei oder Verzeichnis 
       nicht gefunden
    * find: "/etc/postgresql/8.3/main/pg_hba.conf": Datei oder Verzeichnis 
       nicht gefunden
    * find: "/etc/postgresql/8.4/main/pg_hba.conf": Datei oder Verzeichnis 
      nicht gefunden
    * find: "/etc/postgresql/9.1/main/pg_hba.conf": Datei oder Verzeichnis 
      nicht gefunden
    * dpkg-query: Kein Paket gefunden, das auf ldapacl_66univention-
      appcenter_app.acl passt
    * Benutzer postgres wird zur Gruppe ssl-cert hinzugefügt.
    * Building PostgreSQL dictionaries from installed myspell/hunspell 
      packages...
    * /bin/echo: Schreibfehler: Ungültiger Dateideskriptor
    * close failed in file object destructor:
    * sys.excepthook is missing
    * lost sys.stderr
Comment 8 Felix Botner univentionstaff 2016-08-22 17:18:45 CEST
Please add a "database_passwordfile" parameter, if set, store the database password in this file in the container
Comment 9 Felix Botner univentionstaff 2016-08-24 14:18:49 CEST
A restart of the database (postgresql) is missing after the pg_hba configuration.

New system, installed horde docker app

-> univention-app shell horde /usr/share/univention-mail-horde/univention-mail-horde-configuration "Administrator"
...
Fatal Error:
Could not instantiate PDO. PDOException: SQLSTATE[08006] [7] FATAL:  kein pg_hba.conf-Eintrag f?r Host >>172.17.0.1<<, Benutzer >>horde<<, Datenbank >>hordedb<<, SSL an
FATAL:  kein pg_hba.conf-Eintrag f?r Host >>172.17.0.1<<, Benutzer >>horde<<, Datenbank >>hordedb<<, SSL aus
In /usr/share/php/Horde/Db/Adapter/Pdo/Base.php on line 48

-> /etc/init.d/postgresql restart
-> univention-app shell horde /usr/share/univention-mail-horde/univention-mail-horde-configuration "Administrator"
Comment 10 Felix Botner univentionstaff 2016-08-26 11:23:40 CEST
(In reply to Felix Botner from comment #7)
 
> > DB_HOST is set to "$fqdn:$port". Don't know if this is very clever. Maybe one 
> > needs a dedicated DB_PORT?
> 
> This is not good, i think we need a dedicated variables for host and port,
> most application configs have separate options for the db host and port ...

i have added the DB_PORT stuff (needed for a gitlab-ee docker app tests)
univention-appcenter r71963

feel free to improve/fix/modify as long as we keep the dedicated DB_PORT (and DockerEnvDatabasePort) and DB_HOST variables
Comment 11 Dirk Wiesenthal univentionstaff 2016-09-06 02:38:43 CEST
univention-appcenter 5.0.22-9.215.201609060234

Database password file is copied to the container. Removed warning when installing in UMC. Postgres is restarted once and docker0 is always allowed even when no Docker App is installed (just like MySQL firewall).

Also added %(database)s/autostart=no to the container.
Comment 12 Felix Botner univentionstaff 2016-09-07 11:19:18 CEST
Created attachment 7984 [details]
appcenter.log.gz

FAIL - mysql

modified horde app to use mysql
-> univention-app install horde
...
univention-mysql installiert
dpkg-query: Kein Paket gefunden, das auf ldapacl_66univention-appcenter_app.acl passt
Could not install software packages
Aborting...
Going to remove Horde Groupware Webmail Edition (5.2.7-2)
...

see attachment for complete log

you can use the horde app in appcenter-test to verify the mysql database integration

FAIL - database password file

I think we need something like this

app.ini
databasepasswordfile = /etc/horde.secret

The database password is stored in this file in the container.

OK - postgres restart
OK - postgres
OK - host port
Comment 13 Felix Botner univentionstaff 2016-09-07 12:33:02 CEST
OK - mysql, no access to datbase

mysql -u horde -p 
Enter password: 
ERROR 1045 (28000): Access denied for user 'horde'@'localhost' (using password: YES)


OK - mysql installation
OK - password file
Comment 14 Felix Botner univentionstaff 2016-09-07 12:33:19 CEST
(In reply to Felix Botner from comment #13)
> OK - mysql, no access to datbase
-> FAIL
Comment 15 Dirk Wiesenthal univentionstaff 2016-09-07 15:08:41 CEST
univention-appcenter 5.0.22-12.218.201609071505

I have tested it with dudle locally set to MySQL. On the host, I did:

mysql -u dudle -D dudle -h master50 -p
Comment 16 Felix Botner univentionstaff 2016-09-07 16:13:29 CEST
OK -mysql
OK - psql

But see Bug #42307, univention-mysql does not set bind-address, to 127.0.0.1 is used and the containers can't connect to the mysql-server

Workaround:
 set bind_address to 0.0.0.0 (or whatever, as long as the containers have access)

OK - yaml
Comment 17 Janek Walkenhorst univentionstaff 2016-09-07 18:41:39 CEST
<http://errata.software-univention.de/ucs/4.1/247.html>