On-the-fly database migration between two hosts (no dump file)

Hi, world!

Yesterday I had to migrate a MySQL database from on host to another. I didn't want to follow the standard process of (1) dumping a file, (2) transferring it and then (3) loading it in the new host for a few reasons:

• The source host was low on space and the database was big
• This is a waste of time because dump and load does not run in parallel
• I like to do complicated stuff ;)

So, the idea was to use some pipes and redirect the data flow through SSH to the target host, which would receive it and load on the new database.
I did it with MySQL, but it should work with any database that supports dumping to the standard output and loading dumps from standard input.

Requirements

• Have two Linux hosts with SSH available
• Have pv and gzip installed (pv is not actually required)
• Need to transfer a huge database between them

Transfering

You have to run one command in each host. I'll call the host that currently contains the database to be transferred source host and the host that will receive the new database as target host. You need to start the with the target host as it will wait from the connection from the source host.
I recommend you to run both sides inside a screen shell to avoid disruption by network problems if you are accessing the hosts remotely.

Target host

The command in the target host is the following (broken in lines for the ease of reading):

nc -l 3456 | \
  gunzip | \
  pv | \
  mysql -u tdb_user -ptdb_pass targetdatabase

This command receives the data from the source host and then flows it through some process. The nc command waits a connection listening on port 1234. When it receives a connection, it starts sending data to gunzip, which decompress the data. pv is just to show how much data has passed through it. Finally, mysql loads the data it receives from standard input in the targetdatabase. Keep in mind that this only starts when the other side connects and sends data.

Source host

The command in the source host is the following (broken in lines for the ease of reading):

mysqldump -u sdb_user -psdb_pass sourcedatabase | \
  pv | \
  gzip | \
  ssh sshuser@targethost nc 127.0.0.1 3456

This command sends data to the target host. Firstly, mysqldump extracts the data from sourcedatabase and sends it to the standard output. pv receives it and only shows how much data is passing through. Then, gzip process compresses the data and ssh sends it to the other side. Notice that SSH is running a nc remotely. This nc is responsible for connecting in the nc you started on the target host and plug the two sides.

That's it!

If everything is all right, in some time the database will be already copied to the target host! In almost half the time (actually, the total time is expected to be the load time on the target host).

If you have any doubt or suggestion, please post in the comments!

Changing Samba4 (AWS Simple AD) user password using Java

I recently had to setup a AWS Simple AD to replace an old OpenLDAP that served us for years. I may post more about this in the following days, but if you have interest in any specific topic, please post here in the comments and I can speed it up ;)

Before

During this migration, I had to adjust a simple app I did in the past to reset and change users' passwords. To set a user's password, this application was just calculating the new password and setting in the userPassword attribute, like this:

LdapConnection connection = new LdapNetworkConnection( ldapserver, 389, false )) {
connection.bind(user, pass);

EntryCursor cursor = connection.search( baseDN, "(uid=" + user.getUsername() + ")", SearchScope.ONELEVEL, "*" );

if ( !cursor.next() ) {
  logger.debug("no user with uid '{}' found.", user.getUsername());
  return false;
}

Entry entry = cursor.get();

byte[] newPass = PasswordUtil.createStoragePassword(user.getPassword(), LdapSecurityConstants.HASH_METHOD_SSHA);

entry.put("userPassword", newPass);
entry.put("shadowLastChange", new Date().getTime() / (1000 * 60 * 60 * 24) + "");

logger.debug("changing password for user '{}'...", user.getUsername());
connection.modify(entry.getDn(), 
  extractModification("userPassword", entry), 
  extractModification("shadowLastChange", entry)
); 
 
connection.unBind();

The problem

When checking the Simple AD user's attributes, there was no userPassword anymore, so I tried to find a way to change the password. As I was already using de Apache LDAP API, and it supports LDAP Password Modify Extended Operation, it seemed a good way to go as I wouldn't need to worry about implementation-specific problems.

I just forgot to check if Simple AD/Samba4 supports it :( And it didn't, AFAICS.

Believe me, I tried a lot of code combinations to use the 1.3.6.1.4.1.4203.1.11.1 extended operation as there is no doc (and even reported a bug), but could not make it work.

There is hope

After spending a lot of time trying to figure it out, I found this archive:

http://linux.samba.narkive.com/3nPys6p5/samba-sambar4-user-creation-with-ldap-and-initial-password

There, Tomas Mueller says:

I do not have a AD available today , i'll try tomorrow. i've found this
about the userPassword attribute on msdn:
http://msdn.microsoft.com/en-us/library/cc223249(prot.20).aspx
<http://msdn.microsoft.com/en-us/library/cc223249%28prot.20%29.aspx>

searching the sourcecode about userPassword i've found this comment in
password_hash.c:

* Notice: unlike the real AD which only supports the UTF16 special based
* 'unicodePwd' and the UTF8 based 'userPassword' plaintext attribute we
* understand also a UTF16 based 'clearTextPassword' one.
* The latter is also accessible through LDAP so it can also be set by
external
* tools and scripts. But be aware that this isn't portable on non
SAMBA 4 ADs!

"The latter is also accessible through LDAP" implies that unicodePwd and
userPassword aren't.

- Thomas

So, after reading this, I tried clearTextPassword and it worked!!! It seems that Samba4 doesn't implement the extended operation, but created this fake attribute to allow password set through LDAP protocol.

Follows the base of the final code:

LdapConnection connection = new LdapNetworkConnection( ldapserver, 389, false )) {
connection.bind(user, pass);

SearchRequest searchRequest = getUserSearch(user);
SearchCursor cursor = connection.search( searchRequest );
if ( !cursor.next() ) {
  logger.debug("no user with sAMAccountName '{}' found.", user.getUsername());
  throw new InvalidCredentialsException();
}

Entry entry = cursor.getEntry();
Dn userBeingChangedDN = entry.getDn();

logger.debug("changing password for user '{}'...", user.getUsername());
entry.put("clearTextPassword", newPassword.getBytes("UTF-16LE"));
connection.modify(entry.getDn(), extractModification("clearTextPassword", entry));

try (LdapConnection connectionForChange = getConnection()) {
  connectionForChange.bind(userBeingChangedDN, user.getPassword());
  connectionForChange.unBind();
} catch (LdapException e) {
  throw new InvalidCredentialsException();
}

connection.unBind();

Hope it helps!

If you want to know more about the whole Simple AD setup, please comment asking what you want.

Extras

private static Modification extractModification(String attrAlias, Entry modifiedEntry) {
  Modification mod = new DefaultModification();
  mod.setOperation(ModificationOperation.REPLACE_ATTRIBUTE);
  Attribute attribute = modifiedEntry.get(attrAlias);
  mod.setAttribute(attribute);
  return mod; 
}

private SearchRequest getUserSearch(User user) throws LdapInvalidDnException, LdapException {
  SearchRequest searchRequest = new SearchRequestImpl();
  searchRequest.setBase( new Dn("CN=Users,DC=visagio,DC=company") );
  searchRequest.setFilter( "(sAMAccountName=" + user.getUsername() + ")" );
  searchRequest.setScope( SearchScope.ONELEVEL );
  searchRequest.addAttributes( "*" );
  return searchRequest;
}

Formatting code on Blogger

In my last post, I needed to format some code. I didn't want to spend much time with this, so I followed the first reasonable blog post I found:

http://www.craftyfella.com/2010/01/syntax-highlighting-with-blogger-engine.html

I may have worked perfectly in 2010, but currently there are a few brushes missing in the .js import snippet. I created a new import snippet containing all .js brushes listed on http://alexgorbatchev.com/SyntaxHighlighter/manual/brushes/. Follows:

    <link href='http://alexgorbatchev.com/pub/sh/current/styles/shCore.css' rel='stylesheet' type='text/css'/> 
    <link href='http://alexgorbatchev.com/pub/sh/current/styles/shThemeEmacs.css' rel='stylesheet' type='text/css'/> 
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shCore.js' type='text/javascript'></script> 
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushAS3.js' type='text/javascript'></script> 
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushBash.js' type='text/javascript'></script> 
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushColdFusion.js' type='text/javascript'></script> 
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushCSharp.js' type='text/javascript'></script> 
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushCpp.js' type='text/javascript'></script> 
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushCss.js' type='text/javascript'></script> 
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushDelphi.js' type='text/javascript'></script> 
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushDiff.js' type='text/javascript'></script> 
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushErlang.js' type='text/javascript'></script> 
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushGroovy.js' type='text/javascript'></script> 
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushJScript.js' type='text/javascript'></script> 
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushJava.js' type='text/javascript'></script>
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushJavaFX.js' type='text/javascript'></script> 
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushPerl.js' type='text/javascript'></script> 
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushPhp.js' type='text/javascript'></script> 
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushPlain.js' type='text/javascript'></script> 
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushPowerShell.js' type='text/javascript'></script> 
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushPython.js' type='text/javascript'></script> 
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushRuby.js' type='text/javascript'></script> 
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushScala.js' type='text/javascript'></script> 
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushSql.js' type='text/javascript'></script> 
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushVb.js' type='text/javascript'></script> 
    <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushXml.js' type='text/javascript'></script> 
    <script language='javascript'> 
      SyntaxHighlighter.config.bloggerMode = true;
      SyntaxHighlighter.config.clipboardSwf = 'http://alexgorbatchev.com/pub/sh/current/scripts/clipboard.swf';
      SyntaxHighlighter.all();
    </script>

The available brushes are:

• ActionScript3 - as3, actionscript3
• Bash/shell - bash, shell
• ColdFusion - cf, coldfusion
• C# - c-sharp, csharp
• C++ - cpp, c
• CSS - css
• Delphi - delphi, pas, pascal
• Diff - diff, patch
• Erlang - erl, erlang
• Groovy - groovy
• JavaScript - js, jscript, javascript
• Java - java
• JavaFX - jfx, javafx
• Perl - perl, pl
• PHP - php
• Plain Text - plain, text
• PowerShell - ps, powershell
• Python - py, python
• Ruby - rails, ror, ruby
• Scala - scala
• SQL - sql
• Visual Basic - vb, vbnet
• XML - xml, xhtml, xslt, html, xhtml

Hope it helps!

EDIT:

I overlooked the fact that SyntaxHighlighter have a lot of themes, choose yours ;)
http://alexgorbatchev.com/SyntaxHighlighter/manual/themes/

Redmine AD SSO setup

Hello, world!

I was looking for a place to post my journey to setup AD SSO in a Redmine instance I manage and...
After almost 10 years, I found out I still have this blog!
So, let's go!

Objective

Our goal is basically setup the single_auth plugin with the most recently available software. This plugin mentions a mod_ntlm apache module, but I simply couldn't compile it or find a version that works.

My Redmine setup

I have a Redmine 3.1-stable checkout, with a lot of custom plugins, running on CentOS 7 and Apache 2.4. It uses mod_passenger module to serve the app.

Requirements

I don't have any idea of what the requirements really are ;) Follows the list of the softwares I'm currently using (all of them currently available on CentOS repos, rubygems or redmine repo):
CentOS 7.2.1511
Samba 4.2.3
Apache 2.4.6
Ruby 2.2.1p85
Passenger 5.0.18
Redmine 3.1-stable with single_auth plugin installed and LDAP auth already configured

SSO Setup

The idea here is to follow the default setup of the single_auth plugin, but using the mod_auth_ntlm_winbind apache module, which is maintained and currently available on CentOS.

Samba domain join

The mod_auth_ntlm_winbind module requires that samba is installed and joined the domain you want to single-sign-on. It acts just as a bridge between Apache and ntlm_auth program. Thus, the first step is to install and join samba to the domain.

yum install samba-winbind-clients realmd
realm join --verbose --client-software=winbind --user=some_join_privileged_user mydomain.com

To test, execute the following on the shell to test you are able to authenticate a user:

ntlm_auth --username=some_user
Password: *********
NT_STATUS_OK: Success (0x0)

Windind configuration

Depending on your Redmine LDAP configuration, it may expect the username without the domain prefix when looking for new users. In this case (it was mine), the easiest way is to setup winbind to return the user without the domain prefix. Just check it won't affect other systems in the same machine before changing.

vim /etc/samba/smb.conf
# make sure "winbind use default domain" is set to "yes"
systemctl restart winbind

To test, execute the follwing command to check how a user is listed by samba.

wbinfo -u | head -n 1
first_user # (there should be no "DOMAIN\" prefix)

Install the single_auth plugin

This is the last step here, which is a slightly modified version of what is said in the original plugin. Make sure the redmine side is configured as said in this plugin page.

First, install the module package and add the apache user to the group allowed to use the ntlm_auth private pipe.

yum install mod_auth_ntlm_winbind
usermod apache -a -G wbpriv 

The apache site /etc/httpd/conf.d/yoursite.conf should be set up as following:

  <Location "/login">
    AuthName "MySite NTLM Authentication"
    NTLMAuth on
    NegotiateAuth on
    NTLMAuthHelper "/usr/bin/ntlm_auth --helper-protocol=squid-2.5-ntlmssp"
    NegotiateAuthHelper "/usr/bin/ntlm_auth --helper-protocol=gss-spnego"
    NTLMBasicAuthoritative on
    AuthType NTLM
    AuthType Negotiate
    Require valid-user
  </Location>

After that, restart Apache.

service httpd restart

That's it!

All the requests to any Redmine resource should be redirected to /login, which will require a valid user on apache side before handing forward the request to Redmine. When Redmine receives the request, the single_auth plugin will read the REMOTE_USER env var filled by Apache and will auto login the user by searching it on the LDAP auth source, without any user interaction. Single Sign On!

Improvements

It's working ok so far, but I'm looking for one big improvement to this setup: As the apache side requires a valid-user, all users must be on LDAP. Otherwise, Apache keeps asking for a user and password. I would like to foward the request to Redmine even if the login on apache side fails.

My first obvious try was to remove the "valid-user" require, but it failed because Apache detects there is no requirement and doesn't even try to reach the NTLM auth mechanism, making the SSO stop working.

If you have any idea, fell free to post!

[]'s