# Pure-FTPd with Postgresql(solved)

## DouweQuerty

Hi,

I am trying to put the user information in a postgresql database. This has been documented for mysql but not for postgresql. I am kind a stuck so any help would be welcome  :Very Happy: 

emerged as : net-ftp/pure-ftpd-1.0.21-r1  USE="pam postgres ssl vchroot -caps -charconv -ldap -mysql -noiplog -paranoidmsg -xinetd"

/etc/conf.d/pure-ftpd : 

  AUTH="-l pgsql:/etc/pureftpd-pgsql.conf" 

  MISC_OTHER="-d -A -E -i -j -K -r -L 10000:3 -m 30 -O stats:/var/log/pureftpd.log -p 49152:65534 -R -u 1 -U 177:022 -X -y 2:3 -Y 0 -Z"

  ## And some other settings which have nothing to do with authentication

/etc/pureftpd-pgsql.conf :

  PGSQLServer     localhost

  PGSQLPort       5432

  PGSQLUser       SOME_USER

  PGSQLPassword  SOME_PASS

  PGSQLDatabase   pureftpd

  PGSQLCrypt      cleartxt

  PGSQLGetPW          SELECT "Password" FROM "users" WHERE "User"='\L'

  PGSQLGetUID         SELECT "Uid" FROM "users" WHERE "User"='\L'

  PGSQLGetGID         SELECT "Gid" FROM "users" WHERE "User"='\L'

  PGSQLGetDir         SELECT "Dir" FROM "users" WHERE "User"='\L'

  PGSQLGetQTAFS       SELECT "QuotaFiles" FROM "users" WHERE "User"='\L'

  PGSQLGetQTASZ       SELECT "QuotaSize" FROM "users" WHERE "User"='\L'

  PGSQLGetRatioUL     SELECT "ULRatio" FROM "users" WHERE "User"='\L'

  PGSQLGetRatioDL     SELECT "DLRatio" FROM "users" WHERE "User"='\L'

  PGSQLGetBandwidthUL SELECT "ULBandwidth" FROM "users" WHERE "User"='\L'

  PGSQLGetBandwidthDL SELECT "DLBandwidth" FROM "users" WHERE "User"='\L'

The postgresql user and database are created and table is created by :

    CREATE TABLE "users" (

  "User" VARCHAR(16) NOT NULL,

  "Password" VARCHAR(64) NOT NULL,

  "Uid" INTEGER NOT NULL default '-1',

  "Gid" INTEGER NOT NULL default '-1',

  "Dir" VARCHAR(128) NOT NULL,

  "QuotaFiles" INTEGER,

  "QuotaSize" INTEGER,

  "ULRatio"   INTEGER,

  "DLRatio"   INTEGER,

  "ULBandwidth" INTEGER,

  "DLBandwidth"  INTEGER,

  PRIMARY KEY ("User")

) WITHOUT OIDS;

I inserted a test user and tried to connect but it in the logs I read :

Jan 31 01:55:16 dataservertje pure-ftpd: (?@10.1.1.126) [INFO] New connection from 10.1.1.126

Jan 31 01:55:16 dataservertje pure-ftpd: (?@10.1.1.126) [DEBUG] Command [user] [SOME_USER]

Jan 31 01:55:16 dataservertje pure-ftpd: (?@10.1.1.126) [DEBUG] Command [pass] [<*>]

Jan 31 01:55:16 dataservertje pure-ftpd: (?@10.1.1.126) [WARNING] Authentication failed for user [mammie1]

Jan 31 01:55:19 dataservertje pure-ftpd: (?@10.1.1.126) [INFO] Logout.

The pure-ftpd does connect to the postgresql server (if I shut postgresql down the error becomes : Jan 31 01:37:45 dataservertje pure-ftpd: (?@10.1.1.126) [ERROR] The SQL server seems to be down) but somehow fails to get the right info out.  I fired the SQL statements directly into postgresql (with \l replaced by username and added an ';') and they work just fine. The postgresql server is set to allow local connections from the Pure-ftpd user. 

Again please help me to solve this soon. Thnxs for the help,

DouweQuerty

Solved it myself : It appears that ALL values MUST be defined as NOT NULL or pureftpd will think it has a security problem. The only clue was on the pureftpd config page in a single line (no warnings in syslog or what so ever):Security barriers are also implemented to avoid bad implications if wrong data types (eg. binary blobs instead of plain text) are fetched with SQL queries.

Thus if you got the same problem check you table creation queries,

DQLast edited by DouweQuerty on Fri Feb 02, 2007 3:50 pm; edited 1 time in total

----------

## DouweQuerty

Bring

Up

My

Post

DQ

----------

## Zanussi

I have problem with my pure-ftpd. Directly when i type /etc/init.d/pure-ftpd start

i see next sentences

*starting Pure-FTPd ...

*Could not launch Pure-FTPd

I don't know, where is problem.

Can you help me???

----------

## DouweQuerty

After installing make sure the following line is uncommented in your /etc/conf.d/pure-ftpd 

```
IS_CONFIGURED="yes"
```

Else you need to check out your syslog by typing

```
tail -n50 /var/log/syslog | more 
```

Goodluck

DQ

----------

