Category Archives: unix

AppArmor on Ubuntu and MySQL in custom directories

AppArmor profile on Ubuntu is (rightly) restrictive and prevents the daemon binary mysqld from writing to unexpected locations.

So here’s another one in my series of ‘stupid notes to self’ — things that may help a reader, things that will help me, but things that are not proper or full guides to solving a problem.

Starting an already initialized datadir:

normal-user$ /usr/sbin/mysqld --defaults-file=/tmp/barproject-mysql-my.cnf
2018-01-09T21:24:28.090896Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2018-01-09T21:24:28.090907Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable

Initializing a new one:

normal-user$ /usr/sbin/mysqld --defaults-file=/tmp/barproject-mysql-my.cnf --datadir="$(pwd)/mysql" --log-level-verbosity=VERBOSE --initialize-insecure
mysqld: Can't create directory '/home/foo/projects/bar/_dev/mysql/' (Errcode: 13 -     Permission denied)
2016-10-10T16:23:29.515470Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-10-10T16:23:29.519420Z 0 [ERROR] Aborting

(Note that /tmp/barproject-mysql-my.cnf has been created from a template prior to running either of these. It specifies many values including datadir.)

First, I worked under the assumption the daemon was running under the wrong user or that the directory has wrong permissions. However, changing settings to any reasonable value did not get rid of errors with either initialization step or run step.

AppArmor has profiles that may block accesses atypical for the program executed. One such profile is for /usr/sbin/mysqld and is located in /etc/apparmor.d/usr.sbin.mysqld.

I tried symlinking it to /etc/apparmor.d/disable directory and restarting apparmor with systemctl restart apparmor. This didn’t change anything. I also tried whitelisting the directory by adding a local configuration change to /etc/apparmor.d/local/usr.sbin.mysqld:

/home/foo/projects/bar/_dev/mysql/** rwk,

systemctl restart apparmor — i.e. restarting through systemd — did not help.

However telling apparmor to tear itself down using the service command, as well as telling it to reload its profile cache, did:

service apparmor stop
service apparmor teardown
service apparmor recache
service apparmor start

There’s probably a smarter way, but this is good enough for me.

What are OSC terminal control sequences / escape codes?

Changelog for a recent release of SecureShell for Chrome
included a mention of “Support for iTerm2 OSC-9 growl notifications”. This is displayed in terminal when you’re SSHing into a machine, and this entry piqued my interest.

Turns out it’s from hterm’s changelog. And turns out hterm has a nice list of supported control sequences.

OSC are “operating system command” control sequences and there’s a few of them supported by hterm / SecureShell. I find a few super interesting (\033 being ESC in the doc linked above):

  • 0: set window title: echo -e "\033]0;This is the window title\a"
  • 9: show system notification (iTerm2 variant): echo -e "\033]9;This is the notification text\a"
  • 777: show system notification (rxvt-unicode / urxvt variant): echo -e "\033]777;notify;This is the notification title;This is the notification text\a"
  • 52: clipboard operation; hterm only supports c for copy: echo -e "\033]52;c;$(base64 <<< hello)\a"

Changing the default file manager away from Nautilus

Nautilus is opening the desktop, making the experience miserable under i3 when all you’re trying to do is ‘view files in directory’.

XFCE’s Thunar is lightweight, and it doesn’t try to create a desktop window.

To get apps such as Chrome to handle “open directory” commands using Thunar, teach xdg-mime about it:

$ sudo apt-get install thunar
$ xdg-mime query default inode/directory
$ xdg-mime default Thunar.desktop inode/directory  # yep, Thunar's desktop file is titlecased on Debian-derived systems
$ xdg-mime query default inode/directory
$ xdg-open .
...thunar opens...

This is sort-of a follow-up to my previous post on this topic. See also, Debian’s article about usage of MIME types.

But my dog does.

Migrating Prosody from text store and sqlite3 to PostgreSQL


  • You started off from the basic storage config:
    • Regular data is in filesystem.
    • mam (xep0313) message archive is in SQLite3.
  • You want to transition to using just PostgreSQL.
  • PostgreSQL version is 9.4.


# # largest version of pgsql you have
~# apt install postgresql-9.4-client

# # get lua-dbi module for pgsql
~# apt install lua-dbi-postgresql

Creating PostgreSQL user

# # PostgreSQL trusts users connecting over unix domain socket to be the
# # same as their local account.
# # Therefore, become postgres -- the admin account.
user:~$ sudo su postgres

# # run user creation
postgres:~$ createuser --interactive
# # name: prosody
# # no other administrative options

# # run postgresql client
postgres:~$ psql
-- create database

-- give the prosody user all rights on it
GRANT ALL ON DATABASE prosody TO prosody;

-- in case of connecting over network and using md5 trust,
-- set prosody account password:
ALTER ROLE prosody WITH ENCRYPTED PASSWORD 'here_some_password';

Over network?

Assuming you want to connect over the network, edit /etc/postgresql/9.4/main/pg_hba.conf. Append:

# type, database, user, address, auth method
host prosody prosody md5
  • Try to minimize your permitted netmask.
  • Can you configure a more secure auth method than md5? Do so.

Migrate data from filesystem

# # as prosody local user

prosody:$ cd prosody-hg/tools/migrator

# # overwrite the config.
prosody:$ cat > migrator.cfg.lua << _EOF
local data_path = "../../data";

input {
        type = "prosody_files";
        path = data_path;
output {
        type = "prosody_sql";
        driver = "PostgreSQL";
        database = "prosody";
        username = "prosody";
        password = "here_some_password";
        host = ""; -- this assumes network connection; migration with local user credentials was not attempted.

# # run the migrator in ~/prosody-hg/tools/migrator
prosody:$ lua prosody-migrator.lua input output

Migrate mam archive from SQLite3

# # as prosody user

prosody:~$ cd prosody-hg/data

# # Having first verified there is nothing in Prosody table...
prosody:$ sqlite3 prosody.sqlite 'SELECT COUNT(*) FROM prosody;'
# # ...drop prosody table. All its data (roster etc) was until now stored on the filesystem.
prosody:$ sqlite3 prosody.sqlite 'DROP TABLE prosody;'

# # dump and massage the sqlite3 output, piping it into psql.
# # psql authenticates as the 'prosody' user and does not require the password.
prosody:$ sqlite3 prosody.sqlite .dump | \
    grep -v 'BEGIN TRANSACTION;' | \
    grep -v sqlite_sequence | \
    awk '/CREATE TABLE/{gsub("`","\"");} 1' | \
    awk '/CREATE UNIQUE INDEX/{gsub("`","\"");} 1' | \

# # manual step 🙁
# # fix the autoincrement.
prosody:$ sqlite3 prosody.sqlite 'SELECT COUNT(*) FROM prosodyarchive;'
# # use this number + 1 in:
prosody:$ echo 'ALTER SEQUENCE prosodyarchive_sort_id_seq RESTART WITH 123456;' | psql

Update Prosody config

In prosody.cfg.lua:

storage = {
        archive2 = "sql";
sql = { driver = "PostgreSQL", database = "prosody", username = "prosody", password = "here_some_password", host = "" }

Getting sound and other panels in gnome-control-center outside GNOME

Missing some panels in gnome-control-center? No “Online Accounts”? No “Sound”?

Apparently, panels are defined through .desktop files in /usr/share/applications (!), and they mostly have the setting of OnlyShowIn=GNOME; or OnlyShowIn=GNOME;Unity;.

To trigger them appearing in, say, i3, xfce or similar, you need to pretend you’re actually running GNOME by exporting the XDG_CURRENT_DESKTOP variable with the value of GNOME.

XDG_CURRENT_DESKTOP=GNOME gnome-control-center

Vidalia crashing on Ubuntu 14.04 due to incorrect AppArmor profile

This has been going unfixed for years now.

For me, the fix involved adding the following into /etc/apparmor.d/usr.bin.vidalia.

/usr/share/glib-2.0/schemas/ r,
/usr/share/glib-2.0/schemas/** r,

Then, I reloaded AppArmor /etc/init.d/apparmor reload and the contraption worked. If you get asked about your Tor control password, restart tor /etc/init.d/tor restart.