Re: [Proftpd-user] Proftpd, mod_Sql and unknown file owners

[TJ S. <tj...@ca...>]

> We have a proftpd setup with mod_sql + unixodbc that authenticates users 
> against a oracle database with a named query. This works great and users 
> can login no problem.
>
> Recently I noticed that if a directory contains files created from 
> windows, getting the 1000254 or something uid, proftpd or mod_sql does a 
> "default" query which fail since we don't have any "users" table in the 
> database. This makes mod_sql error out and the user is disconnected.
>
> The users in question have access to the files through the gid anyway 
> but gets disconnected so.

I'm assuming that your mod_sql configuration looks something like this:

  SQLNamedQuery get-user-by-name SELECT "... WHERE userid = '%U'"
  SQLUserInfo custom:/get-user-by-name

specifically, that you're using only one custom query, that looks up user info by name.

If so, then you are encountering the situation mentioned in the SQLUserInfo docs:

  http://www.proftpd.org/docs/contrib/mod_sql.html#SQLUserInfo

under the "Custom Queries" section, where it mentions also needing a custom query for retrieving user info by UID, lest you encounter a "Table not found" error (which sounds like the case).

> Any idea why this happends? And what to do about it? We have users 
> accessing the filearea from both Windows and Linux so it's hard to keep 
> those pesky uid's away. Any way to disable this or work around it?

This happens because most directory listings want to see the file ownership by name (user and group), rather than showing the ownership by IDs.  In order to look up the given user/group names for file ownership IDs (which are all that are stored in the filesystem), ProFTPD needs to query the database for the names, given the IDs.

There's no easy way to disable this ID-to-name lookup, no.  There are a couple of workarounds.  First, you can use the AuthOrder directive, to tell ProFTPD to use both mod_sql and mod_auth_unix (the /etc/passwd, /etc/group files) for such name/ID resolution:

  AuthOrder mod_sql.c mod_auth_unix.c

Second, you can supply another custom query, and hardcode its values (thus not needing an "ftpusers" table):

  SQLNamedQuery get-user-by-name SELECT "... WHERE userid = '%U'"

  # We return a hardcoded user name of "unknown", an empty password, and a hardcoded
  # group name of "unknown", and hardcoded numeric UID/GID values.
  SQLNamedQuery get-user-by-id SELECT "'unknown', '', 1001, 1001, '', ''"

  SQLUserInfo custom:/get-user-by-name/get-user-by-id

You may need to tweak the ID-specific custom query, but hopefully this gives you some ideas of how you might address the situation.

Hope this helps,
TJ