PAM/Postgres/Django/ProFTPd example

proftpd.conf.head

# /etc/stuff/proftpd.conf.head

Port            22022
SQLConnectInfo  stuff@127.0.0.1 user pass

# service run as ...
User            nobody
Group           nogroup

# effective ftp user
SQLDefaultUID   300
SQLDefaultGID   2301

stuff

#%PAM-1.0
# /etc/pam.d/stuff

auth        required    pam_pgsql.so config_file=/etc/stuff.pam_pgsql.conf
account     required    pam_pgsql.so config_file=/etc/stuff.pam_pgsql.conf
session     required    pam_pgsql.so config_file=/etc/stuff.pam_pgsql.conf
password    required    pam_pgsql.so config_file=/etc/stuff.pam_pgsql.conf

stuff.pam_pgsql.conf

# /etc/stuff.pam_pgsql.conf

# with NSS, sslmode=enable leads to deadlock; we are only using PAM here, but
# SSL is not needed for localhost
connect = host=127.0.0.1 dbname=stuff user=user password=pass connect_timeout=1 sslmode=disable

# passwords are not really clear ... auth_query preforms the actual hashing
# and comparison from within the query; if that succeeds the original password
# is returned (clear == clear)
pw_type = clear

# pam_pgsql does not support much beyond basic hashing; use pgcrypto to perform
# the comparison, in-query, then return the original to signal success
auth_query = SELECT CAST(%p as varchar) FROM auth_user au, auth_user_groups aug, auth_group ag, auth_group_permissions agp, auth_permission ap, django_content_type ct WHERE (au.username = %u AND au.id = aug.user_id AND aug.group_id = ag.id AND ag.id = agp.group_id AND agp.permission_id = ap.id AND ap.codename = 'use_api' AND ap.content_type_id = ct.id AND ct.app_label = 'stuff' AND ct.model = 'global') AND substring(au.password from 4) = crypt(%p, substring(au.password from 4)) ORDER BY au.username LIMIT 1

# compatible with Django bcrypt
pwd_query = UPDATE auth_user SET password = concat('bc$', crypt(%p, gen_salt('bf', 12))) WHERE username = %u

#TODO: check is_staff and is_active
acct_query = SELECT False, False, False;

stuff.proftpd.conf

# /etc/stuff.proftpd.conf

# load modules ...
<IfModule !mod_sftp.c>
    LoadModule mod_sftp.c
</IfModule>
<IfModule !mod_sftp_pam.c>
    LoadModule mod_sftp_pam.c
</IfModule>
<IfModule !mod_sql.c>
    LoadModule mod_sql.c
</IfModule>
<IfModule !mod_sql_postgres.c>
    LoadModule mod_sql_postgres.c
</IfModule>

# server debug
DebugLevel      0

# server log
TransferLog /var/log/proftpd/xferlog
SystemLog   /var/log/proftpd/proftpd.log

# server run as ...
User            nobody
Group           nogroup

# disable default FTP
Port            0

# server defaults
ServerType      standalone
MaxInstances    30
UseIPv6         off
UseReverseDNS   off

# note: defered until contention becomes an issue
# TODO: per docs, this "can" be disabled ... fail
#
# scoreboard in RAM; existing path, not world-writable
#ScoreboardScrub     on
#ScoreboardFile      /dev/shm/proftpd/proftpd.scoreboard
#ScoreboardMutex     /dev/shm/proftpd/proftpd.scoreboard.lck

<VirtualHost 0.0.0.0>

    # instance config (head)
    #   note: this is a pattern (silent skip)
    Include /etc/stuff/proftpd.conf.hea[d]

    # service debug
    DebugLevel          0

    # service ident
    ServerName          "Stuff SFTP"
    #Port                (provided by include)
    #Protocols           sftp

    # service defaults
    RootLogin           off
    DefaultRoot         ~
    DirFakeUser         on
    DirFakeGroup        on
    CreateHome          on 700 skel /etc/stuff/sftp-skel
    ListOptions         "-a"
    WtmpLog             off
    Umask               022
    TimeoutStalled      300
    DeferWelcome        on
    DisplayLogin        welcome.msg

    # use custom/isolated PAM service name
    AuthPAM             off
    SFTPPAMEngine       on
    SFTPPAMServiceName  stuff
    AuthOrder           mod_sql.c mod_sftp_pam.c*

    # SFTP options
    SFTPEngine On
    SFTPCompression on
    SFTPHostKey /etc/ssh/ssh_host_rsa_key
    SFTPHostKey /etc/ssh/ssh_host_dsa_key
    SFTPHostKey /etc/ssh/ssh_host_ecdsa_key
    #SFTPClientAlive 3 15

    # ensure server defaults are used instead
    SQLMinUserUID   65536
    SQLMinUserGID   65536

    # useless ...
    IdentLookups        off
    UseFtpUsers         off
    RequireValidShell   off

    # allow file overwriting and upload/download resuming
    AllowRetrieveRestart    on
    AllowStoreRestart       on
    AllowOverwrite          on

    # not really used; PAM is authortative
    SQLAuthTypes        plaintext

    # user lookups via postgresql
    SQLEngine           auth
    SQLBackend          postgres
    #SQLConnectInfo      (provided by include)
    SQLNegativeCache    on

    SFTPOptions IgnoreSFTPUploadPerms

    # link auth to custom queries
    SQLAuthenticate     users groups usersetfast groupsetfast
    SQLUserInfo         custom:/get-user-by-name/get-user-by-id/get-user-names/get-all-users
    SQLGroupInfo        custom:/get-group-by-name/get-group-by-id/get-group-by-member/get-all-groupnames/get-all-groups

    # access Django and extract directory information
    SQLNamedQuery       get-user-by-name \
                        SELECT \
"username, 'NOT_USED', id+10000, id+10000, concat('/usr/local/share/stuff/sftp/users/', id+10000), '/bin/false' FROM auth_user WHERE username = '%U'"

    SQLNamedQuery       get-user-by-id \
                        SELECT \
"username, 'NOT_USED', id+10000, id+10000, concat('/usr/local/share/stuff/sftp/users/', id+10000), '/bin/false' FROM auth_user WHERE id = %{0}-10000"

    SQLNamedQuery       get-user-names \
                        SELECT \
"username FROM auth_user"
    SQLNamedQuery       get-all-users \
                        SELECT \
"username, 'NOT_USED', id+10000, id+10000, concat('/usr/local/share/stuff/sftp/users/', id+10000), '/bin/false' FROM auth_user"
    SQLNamedQuery       get-group-by-name \
                        SELECT \
"ag.name AS groupname, ag.id+10000 AS gid, au.username AS members FROM auth_user_groups AS aug INNER JOIN auth_user AS au ON aug.user_id = au.id INNER JOIN auth_group AS ag ON aug.group_id = ag.id WHERE ag.name = '%{0}' LIMIT 1"
    SQLNamedQuery       get-group-by-id \
                        SELECT \
"ag.name AS groupname, ag.id+10000 AS gid, au.username AS members FROM auth_user_groups AS aug INNER JOIN auth_user AS au ON aug.user_id = au.id INNER JOIN auth_group AS ag ON aug.group_id = ag.id WHERE ag.id+10000 = %{0}"
    SQLNamedQuery       get-group-by-member \
                        SELECT \
"ag.name AS groupname, ag.id+10000 AS gid, au.username AS members FROM auth_user_groups AS aug INNER JOIN auth_user AS au ON aug.user_id = au.id INNER JOIN auth_group AS ag ON aug.group_id = ag.id WHERE au.username = '%{0}'"
    SQLNamedQuery       get-all-groupnames \
                        SELECT \
"name FROM auth_group"
    SQLNamedQuery       get-all-groups \
                        SELECT \
"ag.name AS groupname, ag.id+10000 AS gid, au.username AS members FROM auth_user_groups AS aug INNER JOIN auth_user AS au ON aug.user_id = au.id INNER JOIN auth_group AS ag ON aug.group_id = ag.id"

    # forbid SITE CHMOD
    <Limit SITE_CHMOD>
      DenyAll
    </Limit>

    # instance config (tail)
    #   note: this is a pattern (silent skip)
    Include /etc/stuff/proftpd.conf.tai[l]

</VirtualHost>