I am currently trying to set up a pure-ftpd server with PostgreSQL authentication, but I am unable to get my FTP server to communicate with my database during login.
For background, my setup consists of a Linux machine running Ubuntu Server 22.04 LTS that is hosting the pure-ftpd server at IP 192.168.120.98. The PostgreSQL database is hosted on a separate server at IP 192.168.120.99. I have disabled the firewalls on both servers and can ping successfully between them.
However, when I try to login to the FTP server, it is unable to authenticate against the database. I have configured pure-ftpd and the PostgreSQL server based on documentation, but cannot determine why they are not connecting. Any suggestions on troubleshooting this connectivity issue between pure-ftpd and PostgreSQL would be appreciated.
1- I installed the 'pure-ftpd-postgresql' package:
sudo aptitude install pure-ftpd-postgresql
2 - I added these files to the '/etc/pure-ftpd/conf' folder:
echo 'yes' > VerboseLog
echo 'yes' > ChrootEveryone
echo 'yes' > CreateHomeDir
echo 'yes' > UnixAuthentication
3 - I added the 'ftpgroup' and 'ftpuser' groups:
groupadd -g 2001 ftpgroup
useradd -u 2001 -s /bin/false -d /bin/null -c "pureftpd user" -g ftpgroup ftpuser
4 - I modified the configuration file '/etc/pure-ftpd/db/postgresql.conf' to look like this:
PGSQLServer 192.168.120.99
PGSQLPort 5432
PGSQLUser pureftpd
PGSQLPassword admin
PGSQLDatabase custom_db
PGSQLCrypt cleartext
PGSQLGetPW SELECT "password" FROM users WHERE "username"='\L'
PGSQLGetUID SELECT "uid" FROM users WHERE "username"='\L'
PGSQLGetGID SELECT "gid" FROM users WHERE "username"='\L'
PGSQLGetDir SELECT "dir" FROM users WHERE "username"='\L'
5 - I applied the changes:
sudo /etc/init.d/pure-ftpd-postgresql restart
6 - In the PostgreSQL database, I created a user named 'pureftpd' with the password 'admin' and granted full privileges to this user.
7 - I created a database called 'custom_db':
CREATE TABLE users (
id int NOT NULL PRIMARY KEY,
username varchar NOT NULL default '',
Password varchar NOT NULL default '',
Uid int NOT NULL default '2001',
Gid int NOT NULL default '2001',
Dir varchar NOT NULL default '',
QuotaSize int NOT NULL default '0',
ULBandwidth int NOT NULL default '0',
DLBandwidth int NOT NULL default '0',
UNIQUE KEY username (username)
);
8 - I created a user called 'test' with password 'test' and home directory '/home/test'
9 - Now when I try to connect to the FTP server with FileZilla using user 'test', I get:
Status: Connecting to 192.168.1.98:21...
Status: Connection established, waiting for welcome message...
Response: 220---------- Welcome to Pure-FTPd [privsep] [TLS] ----------
Response: 220-You are user number 1 of 50 allowed.
Response: 220-Local time is now 21:52. Server port: 21.
Response: 220-This is a private system - No anonymous login
Response: 220-IPv6 connections are also welcome on this server.
Response: 220 You will be disconnected after 15 minutes of inactivity.
Command: AUTH TLS
Response: 500 This security scheme is not implemented
Command: AUTH SSL
Response: 500 This security scheme is not implemented
Status: Server does not support non-ASCII characters.
Command: USER test
Response: 331 User test OK. Password required
Command: PASS ****
Error: Connection closed by server
Error: Failed to connect to server
10 - When I check the logs with 'journalctl | grep ftp', I get:
Jul 28 21:52:48 pure-ftpd-postgresql pure-ftpd[19921]: (?@192.168.1.99) [INFO] New connection from 192.168.1.99
Jul 28 21:52:48 pure-ftpd-postgresql pure-ftpd[19921]: (?@192.168.1.99) [DEBUG] Command [auth] [TLS]
Jul 28 21:52:48 pure-ftpd-postgresql pure-ftpd[19921]: (?@192.168.1.99) [DEBUG] Command [auth] [SSL]
Jul 28 21:52:48 pure-ftpd-postgresql pure-ftpd[19921]: (?@192.168.1.99) [DEBUG] Command [user] [test]
Jul 28 21:52:48 pure-ftpd-postgresql pure-ftpd[19921]: (?@192.168.1.99) [DEBUG] Command [pass] [<*>]
Jul 28 21:52:48 pure-ftpd-postgresql kernel: pure-ftpd-postg[19921]: segfault at 1538 ip 00007fd8560889fa sp 00007ffe15f2b1f8 error 4 in libc.so.6[7fd855ff6000+195000]
Jul 28 21:52:48 pure-ftpd-postgresql kernel: Code: f3 0f 1e fa 66 0f ef c0 66 0f ef c9 66 0f ef d2 66 0f ef db 48 89 f8 48 89 f9 48 81 e1 ff 0f 00 00 48 81 f9 cf 0f 00 00 77 66 <f3> 0f 6f 20 66 0f 74 e0 66 0f d7 d4 85 d2 74 04 0f bc c2 c3 48 83
Jul 28 21:54:41 pure-ftpd-postgresql sudo[19846]: pam_unix(sudo:session): session closed for user root
I also cannot log in with native Unix users - the same login error occurs. However, I found that if I remove or rename the 'PGSQLConfigFile' at '/etc/pure-ftpd/conf' and restart pure-ftpd, I am then able to log in using Unix accounts. This points to the PostgreSQL configuration being the specific cause, since pure-ftpd functions normally with Unix users when the PostgreSQL file is not present.
To confirm, I was previously able to get pure-ftpd working with MySQL authentication by using the 'pure-ftpd-mysql' package and modifying the mysql.conf file in '/etc/pure-ftpd/db'.
So the PostgreSQL configuration seems to be the crux of the issue. I'm following the same steps as the MySQL setup, which works fine. But with PostgreSQL, I cannot get past the login error despite verbose logging enabled. I've been stuck on this for several days now, so any help or debugging suggestions are appreciated! I really need the pure-uploadscript capability that pure-ftpd provides. Thanks in advance for any guidance you can provide
