Re: [Proftpd-user] mod_sql backend error: could not lookup local user ID

Hi all

Below are the relevant configuration fragments we use. 

The update to 1.3.5e did not really help, we still see the issue.
We can not currently deploy 1.3.6 on this production system.

Some notes to our configuration
* Users are all in the postgres DB, no OS users
* We use HiddenStore
* We use fake user and group information
* SQLUserInfo uses custom queries per virtual host to allow service specific lookups

Thanks for your support.

Cheers
-ok


<Global>
  ...

  HiddenStores on
  AllowStoreRestart off
  DeleteAbortedStores on

  # hide real ID, GID and file modes - "~" will use the current users data
  DirFakeGroup on "~"
  DirFakeUser on "~"
  DirFakeMode  0640

  SQLBackend postgres
  SQLConnectInfo ......
  AuthOrder mod_sql.c
  SQLAuthenticate users
  SQLPasswordEngine on
  
  SQLNamedQuery get-user-salt SELECT "u.password_salt from users u where char_length('%{0}') >= 2 AND \
    (select case(select cast(value as boolean) from configurables where name = 'ignore_case_on_login') when true then lower(u.login) else u.login end) = \
    (select case(select cast(value as boolean) from configurables where name = 'ignore_case_on_login') when true then lower('%{0}') else '%{0}' end) \
    OR (lower(u.email) = lower('%{0}'))"
  SQLPasswordUserSalt sql:/get-user-salt
  SQLAuthTypes SHA512

  # we will use high UID/GID values
  SQLMinUserUID 1000
  SQLDefaultGID 1000
  SQLMinUserGID 1000
  SQLDefaultUID 1000

  # get group information from the DB
  SQLGroupInfo custom:/get-group-by-name/get-group-by-id/get-group-by-member/get-all-groupnames/get-all-groups
  SQLNamedQuery get-group-by-name SELECT "g.name, g.system_id, u.login from groups g, users u, groups_users ug where \
          u.id = ug.user_id and g.id = ug.group_id and g.name = '%{0}'"
  SQLNamedQuery get-group-by-id SELECT "g.name, g.system_id, u.login from groups g, users u, groups_users ug where \
          u.id = ug.user_id and g.id = ug.group_id and g.system_id = '%{0}'"
  SQLNamedQuery get-group-by-member SELECT "g.name, g.system_id, u.login from groups g, users u, groups_users ug where \
          u.id = ug.user_id and g.id = ug.group_id and u.login = '%{0}'"
  SQLNamedQuery get-all-groupnames SELECT "g.name from groups g"
  SQLNamedQuery get-all-groups SELECT "g.name, g.system_id, u.login from groups g, users u, groups_users ug where \
          u.id = ug.user_id and g.id = ug.group_id"

  # get user information from the DB
  SQLUserInfo custom:/get-user-by-name/get-user-by-id/get-user-names/get-all-users

  # get_user_by_name is defined in the virtual servers!

  SQLNamedQuery get-user-by-id SELECT "u.login, u.encrypted_password, u.system_id, g.system_id, u.home, \
        '/bin/bash' from users u, groups g, groups_users ug where u.id = ug.user_id and g.id = ug.group_id and u.system_id ='%{0}'"
  SQLNamedQuery get-user-names SELECT "u.login from users u"
  SQLNamedQuery get-all-users SELECT "u.login ,u.encrypted_password, u.system_id, g.system_id, u.home, \
        '/bin/bash' from users u, groups g, groups_users ug where u.id = ug.user_id and g.id = ug.group_id"

</Global>

# ============================
# SFTP - own virtual host for different port
#        http://www.proftpd.org/docs/contrib/mod_sftp.html
# ============================
<IfModule mod_sftp.c>
  <VirtualHost X.X.X.X>
    ServerName "CargoSFTP"
    Port 22
    AuthOrder mod_sql.c
    SFTPEngine on

    # needs services SFTP
    SQLNamedQuery get-user-by-name SELECT "u.login, u.encrypted_password, u.system_id, g.system_id, u.home, \
      '/bin/bash' from users u, groups g, groups_users ug, services s, activated_services us \
      where char_length('%U') >= 2 AND u.id = ug.user_id and g.id = ug.group_id and s.id = us.service_id and us.user_id = u.id AND (\
      (select case(select cast(value as boolean) from configurables where name = 'ignore_case_on_login') when true then lower(u.login) else u.login end) = \
      (select case(select cast(value as boolean) from configurables where name = 'ignore_case_on_login') when true then lower('%U') else '%U'  end) OR \
      lower(u.email) = lower('%U')) \
      AND s.name = 'SFTP' and u.is_locked = false and u.is_admin = false"

    # allow public key
    SQLNamedQuery get-user-authorized-keys SELECT "u.public_key FROM users u, services s, activated_services us \
      where char_length('%U') >= 2 AND u.id = us.user_id and s.id = us.service_id AND s.name = 'SFTP' and u.is_locked = false and u.is_admin = false AND \
      (select case(select cast(value as boolean) from configurables where name = 'ignore_case_on_login') when true then lower(u.login) else u.login end) = \
      (select case(select cast(value as boolean) from configurables where name = 'ignore_case_on_login') when true then lower('%U') else '%U'  end) OR \
      lower(u.email) = lower('%U')"
    SFTPAuthorizedUserKeys sql:/get-user-authorized-keys
    SFTPCompression delayed
    SFTPHostKey /opt/cargo/etc/ssh_dsa_host_key-sftp
    SFTPLog /var/log/sftp.log
    MaxLoginAttempts 6

    # ignore permission setting triggered by the client
    # and disable the KEXINIT optimization at the cost of latency (some client do not like it)
    SFTPOptions IgnoreSFTPUploadPerms IgnoreSCPUploadPerms PessimisticKexinit

    # Only support SFTP protocol version 1 through 3 for WinSCP and CuteFTP
    SFTPClientMatch WinSCP|ClientSftp sftpProtocolVersion 1-3
    SFTPClientMatch ".*WS_FTP.*" channelWindowSize 32MB

    # Ubuntu sends LC_MESSAGES and gets denied otherwise
    SFTPAcceptEnv LC_MESSAGES LANG LOGINUSER

    SFTPDigests hmac-md5 hmac-sha1 hmac-sha2-256 hmac-sha2-512 hmac-ripemd160 hmac-sha1-96 hmac-md5-96
  </VirtualHost>
</IfModule>



> On 21 May 2017, at 22:39, TJ Saunders <[email protected]> wrote:
> 
> 
>> There is no information in the SQLLogFile about any errors, just normal
>> lookups for users, logging to the DB etc.
>> 
>> I did not find the string "could not look up local user ID" in the
>> sources of mod_sql or mod_sql_postgres.
>> 
>> Any ideas what this error "could not look up local user ID 1001: No such
>> file or directory#012" means and where it comes from?
> 
> Could you provide the proftpd.conf you're using, particularly the
> mod_sftp and mod_sql configurations, please?  That configuration will
> help narrow down the possibilities...
> 
> Do you happen to see/encounter this issue when using ProFTPD 1.3.6?
> 
> Cheers,
> TJ
> 
> ------------------------------------------------------------------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
> _______________________________________________
> ProFTPD Users List   <[email protected]>
> Unsubscribe problems?
> http://www.proftpd.org/list-unsub.html


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
ProFTPD Users List   <[email protected]>
Unsubscribe problems?
http://www.proftpd.org/list-unsub.html