Register ODK Aggregate users programmatically using SQL insert

Hello to all you awesome people!

I just discovered this table called _registered_users with the following structure

My question is... Is it possible to register ODK Aggregate users programmatically using SQL insert to the _registered_users table?

If so, how can the values for these columns be generated? Especially BASIC_AUTH_PASSWORD, BASIC_AUTH_SALT, and DIGEST_AUTH_PASSWORD?

Also, what are the other things to consider? e.g.: the user permissions, etc.

I'm really hoping you guys can give a clairvoyant on this. Thanks in advance.

Hi,
i think we can't generate that data

because it's a chain of process we cant step over one of them.

so i would advice you to use the user interface of Aggregate System to add user and grant them Auth.

Best Regards

There currently isn't a great end point for adding users. What others have tried is to use Python scripts with a Selenium web driver.

If you want to do this with SQL, you'll have to look at the Aggregate source. https://github.com/opendatakit/aggregate/blob/HEAD/src/main/java/org/opendatakit/common/security/spring/RegisteredUsersTable.java is a good place to start.

I haven't looked at the Aggregate source in a while, but I vaguely remember that DIGEST_AUTH_PASSWORD = MD5(username:salt:username) where salt is the realm string in /WEB-INF/lib/security.properties and I think that's all you should need for user auth.

As far as how to figure this out, start from a fresh install, create a handful of users with known passwords and it should be pretty easy to reverse engineer.

2 Likes

Hi Yaw, that's a great tip! I will definitely start from what you pointed out. Thanks a bunch

Hi @yanokwa, when you say MD5(username:salt:username) does that mean
MD5( "username-string" . ":" . "salt-string" . ":" . "username-string" ) ?
Note: where . (period sign) was used as the concatenator per se (example).

Thanks again

Yes, and the use of colon in that way is what the HTTP basic auth scheme requires. https://tools.ietf.org/html/rfc2617#section-2 has more.

You should compare what you insert with SQL's MD5 with what you generate with other MD5 algorithms. I vaguely remember that there is a difference in output.

1 Like

Hi @yanokwa, It seems the right digest password formula is MD5( "username-string" . ":" . "realm-string" . ":" . "password-plaintext-string" ). However, how is it possible to find/generate the realm string?

You can find security.server.realm.realmString in the WEB-INF/lib/security.properties.

Hi @yanokwa , I was able now to synthesize the BASIC_AUTH_PASSWORD, BASIC_AUTH_SALT, and the DIGEST_AUTH_PASSWORD.

I also was able to insert records in _user_granted_authority table. Allowing the user to collect and view data. The programmatically created user works when I tried to use it for logging in the ODK Collect. However, when I try it in the ODK Aggregate, it doesn't work.

Is there something I might have missed?

I'm trying to create a PHP-SDK for the ODK Aggregate because I know there are lots of people who also do PHP on the back-end.

Thanks in advance.

My understanding is that changes to _registered_users and _user_granted_authority are all that is needed.

What specifically is not working?

Hi, Abel,
I'm trying to implementent a programmatically users creation. I read all the posts and I was able now to synthesize the DIGEST_AUTH_PASSWORD but I coud't with BASIC_AUTH_SALT, and the BASIC_AUTH_PASSWORD.

Could you please tell me how you did it with the BASIC_AUTH_SALT, and the BASIC_AUTH_PASSWORD.

@camilo_rodriguez, I sent you a private message regarding your query because I'm not sure if it is good to post the whole formula here.

This programmatic functionality is now a hot topic. A couple of months ago, another user even private-messaged me about the formula. Although... the ODK Aggregate is open-source and it can be reverse-engineered, I do not know if posting it in a public forum will make this topic useful or... harmful

I don't think sharing this is harmful. That is, it's in the source code and it's a fairly standard way to generate passwords.

@yanokwa in that case, below are the formulas for synthesizing the important values.

USERNAME = the desired username
PASSWORD = the desired password
BASIC_AUTH_SALT = any generated 8-char alphanumeric value (e.g. "qwertyui")

REALM_STRING = DATABASE_QUERY( "SELECT VALUE FROM _server_preferences_properties WHERE KEY='LAST_KNOWN_REALM_STRING'" )

BASIC_AUTH_PASSWORD = SHA1( PASSWORD + "{" + BASIC_AUTH_SALT + "}" )
DIGEST_AUTH_PASSWORD = MD5( USERNAME + ":" + REALM_STRING + ":" + PASSWORD )

The above values are based on a generic programming language. That is...

  1. You will need to query the so called "realm string" from the database of your ODK Aggregate.
  2. The arguments for SHA1-and-MD5 hash functions are concatenated strings using the plus sign (+) as the concatenator.

For you to be able to register a user, you need to do the following:

  1. Insert a record in the table called "_registered_users" to create a user; then
  2. Insert a record in the table called "_user_granted_authority " to be able to grant a privilege to the newly created user

To the one reading this, make sure to study those mentioned tables thoroughly -- I know you will be able to figure it out.

Goodluck!

1 Like