File: README.PGSQL

package info (click to toggle)
pure-ftpd 1.0.47-3
  • links: PTS
  • area: main
  • in suites: buster
  • size: 3,212 kB
  • sloc: ansic: 29,132; sh: 1,632; makefile: 500; perl: 280
file content (245 lines) | stat: -rw-r--r-- 8,409 bytes parent folder | download | duplicates (2)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245

       ----------------------- PostgreSQL SUPPORT ------------------------


When PostgreSQL is enabled, all account info are fetched from a central
Postgres database.

To compile the server with PostgreSQL support, you first have to build and
install the PostgreSQL client libraries. PostgreSQL is freely available from
http://www.postgresql.org/ and binary packages are included in many major
distributions. But if you choose a binary form, don't forget to also install
the development packages if they are available separately.

Then, configure Pure-FTPd with --with-pgsql and your favorite extra gadgets:


    ./configure --with-pgsql --with-everything


If your PostgreSQL libraries are installed in a special path, you can specify
it like this:


    ./configure --with-pgsql=/opt/pgsql


In this example, headers (like pgsql.h) will be searched in
/opt/pgsql/include and /opt/pgsql/include/pgsql, while related libraries
will be searched in /opt/pgsql/lib and /opt/pgsql/lib/pgsql .

Then, install the server as usual:


                                 make install


 ------------------------ PGSQL CONFIGURATION FILE ------------------------
           

Before running the server, you have to create a configuration file. Why a
configuration file instead of simple command-line options? you may ask.
Because for security reasons, you may want to hide how to connect to your
PostgreSQL server. And as command-line options can be discovered by local users
(with 'ps auxwww' for instance), it's more secure to use a configuration
file for sensitive data. Keep it readable only by root (chmod 600) .

Here's a sample configuration file:

PGSQLServer     localhost
PGSQLPort       5432
PGSQLUser       root
PGSQLPassword   rootpw
PGSQLDatabase   pureftpd
PGSQLCrypt      cleartext
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'

Have a look at the sample pureftpd-pgsql.conf configuration file for
explanations of every keyword.

Save the configuration file anywhere. Let's say /etc/pureftpd-pgsql.conf .

Then, you have to run the pure-ftpd command with '-l pgsql:' (it's an 'ell'
not a 'one') followed by the path of that configuration file. Here's an
example:

pure-ftpd -l pgsql:/etc/pureftpd-pgsql.conf -B


You can mix different authentication methods. For instance, if you want to
use system (/etc/passwd) accounts when an account is not found in a PostgreSQL
database, use -l pgsql:/etc/pureftpd-pgsql.conf -l unix


     ------------------------ TABLES STRUCTURES ------------------------
     
     
Pure-FTPd is very flexible and users can be stored in any way in SQL tables.
You just have to have fields with the following info:

- The user's login.

- The user's password, hashed using argon2, scrypt or crypt(3). SHA1 and MD5
are also supported for legacy reasons, but shouldn't be used any more.
Pure-FTPd also accepts the "any" value for the PGSQLCrypt field.
With "any", all hash functions are sequentially tried.

* RECOMMENDATION: Do not use SHA1, MD5, or, obviously, plaintext. Unless your
system provides a decent crypt() function, use a PostgreSQL function to verify
the hashed password or use argon2/scrypt.

- The system uid to map the user to. This can be a numeric id or a user
name, looked up at run-time.

- The system gid (numeric or not) .

- The home directory.

Here's a dump of a simple table to handle this:

CREATE TABLE "users" (
  "User" TEXT NOT NULL,
  "Password" TEXT NOT NULL,
  "Uid" INTEGER NOT NULL default '-1',
  "Gid" INTEGER NOT NULL default '-1',
  "Dir" TEXT NOT NULL,
  PRIMARY KEY ("User")
) WITHOUT OIDS;

Uid and Gid can be VARCHAR instead of INTEGER if you want to use names instead
of values.

Then, in the pureftpd-pgsql.conf configuration file, you have to provide SQL
templates to fetch the needed info.

Let's take the previous example:

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'

For each query:

\L is replaced by the login of a user trying to authenticate.
\I is replaced by the IP address the client connected to.
\P is replaced by the port number the client connected to.
\R is replaced by the remote IP address the client connected from.
\D is replaced by the remote IPv4 address, as a long decimal number.

You can mix all of these to store info in various tables. For instance, with
\I, you can have a different table for every domain, so that joe@domain1
won't be the same account than joe@domain2 . And with \R, you can restrict
one account to one specific address.

Please note that a login can only contains common characters: A...Z, a...z,
0...9, -, ., _, space, :, @ and ' . For security purposes, other characters
are forbidden.

You can also remove uid and gid fields in your tables and use default
values instead (thus saving useless lookups) . Two directives are
useful to serve that purpose: PGSQLDefaultUID and PGSQLDefaultGID.

Obvious example:

PGSQLDefaultUID 1000
PGSQLDefaultGID 1000

Using these directives overrides PGSQLGetUID and PGSQLGetGID.


           ------------------------ ARGON2 ------------------------


Password hashed with argon2i and argon2id can be used, provided that pure-ftpd
was linked to libsodium.

They are expected to be provided as a string, as returned by the
crypto_pwhash_str() function or by its bindings.


           ------------------------ SCRYPT ------------------------


Password hashed with scrypt can be used, provided that pure-ftpd was linked to
libsodium.

They are expected to be provided in escrypt format, as returned by the
crypto_pwhash_scryptsalsa208sha256_str() function or by its bindings.

For example, the string $7$C6..../....YzvCLmJDYJpH76BxlZB9fCpCEj2AbGQHoLiG9I/VRO1$/enQ.o1BNtmxjxNc/8hbZq8W0JAqR5YpufJXGAdzmf3
would verify the password "test".


     ------------------------ PER-USER SETTINGS ------------------------


Individual settings can be set for every user, using optional queries.

- PGSQLGetQTAFS is the maximal number of files a user can store in his home
directory.

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

- PGSQLGetQTASZ is the maximal disk usage, in Megabytes.

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

- PGSQLGetRatioUL and PGSQLGetRatioDL are optional ratios.

Example:
PGSQLGetRatioUL SELECT "ULRatio" FROM "users" WHERE "User"='\L'
PGSQLGetRatioDL SELECT "DLRatio" FROM "users" WHERE "User"='\L'

- PGSQLGetBandwidthUL and PGSQLGetBandwidthDL are optional upload and
download bandwidth restrictions. Returned values should be in KB/s.

Example:
PGSQLGetBandwidthUL SELECT "ULBandwidth" FROM "users" WHERE "User"='\L'
PGSQLGetBandwidthDL SELECT "DLBandwidth" FROM "users" WHERE "User"='\L'



      ------------------------ ANONYMOUS USERS ------------------------


If you want to accept anonymous users on your FTP server, you don't need to
have any 'ftp' user in the PGSQL directory. But you need to have a system
'ftp' account on the FTP server.


        ------------------------ ROOT USERS ------------------------


If a PGSQL user entry has a root (0) uid and/or gid, Pure-FTPd will refuse
to log them in.

Without this preventive restriction, if your PGSQL server ever gets
compromised, the attacker could also easily compromise the FTP server.

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.


Hint:

PostgreSQL supports views and it's common practice to define a new DB
user, e.g., ftpd and a view of the 'real' user database with just the
bits that the server needs. E.g., if you have virtual domains you
could use:

create view vftpd as select u.vuser, u.domain, u.passwd, d.uid, d.gid,
'/virtual/' || u.domain || '/' || u.vuser || '/./' as homedir
from vusers as u, vdomains as d where u.domain = v.domain;

grant select on vftpd to ftpd;

The definition of homedir shows how views can be used to enforce a
canonical form for home directories - nothing short of defining this
view will allow a user to drop the chroot from their home directory.