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
|
How to use clear or encrypted passwords for PostgreSQL access:
=============================================================
Use lines such as
local all all md5
host all 192.137.23 255.255.255.0 md5
in /etc/postgresql/pg_hba.conf (md5 is to be preferred over password and
crypt methods); then you can use
CREATE USER user WITH ENCRYPTED PASSWORD password...
to create a new user with the specified password, or
ALTER USER user WITH ENCRYPTED PASSWORD password...
to change the password of an existing user. ANY USER with create-user
privilege can ALTER the password of any user, *INCLUDING* that of the
postgres super-user.
If connecting with psql, use the `-U user' option; the user is prompted for
the password. If you don't use -U, the connection fails.
If using your own program with libpq, it is up to you to collect the user name
and password from the user and send them to the backend with PQsetdbLogin().
Non-md5 passwords are stored in pg_shadow in clear, but if `crypt'
authentication is specified, the frontend encrypts the password with a
random salt and the backend uses the same salt to encrypt the password in
the database. If the two encrypted passwords match, the user is allowed
access. If the authentication method is `password', the password is
transmitted and compared in clear.
If passwords are turned on, it becomes impossible to connect as
a user, if no password is defined for that user. Neither can you use
\connect to change user within psql without supplying a password.
To avoid problems with supplying passwords to automatic scripts, the
default Debian configuration is for access to be given to localhost
through TCP/IP with 'ident sameuser' authentication.
If you prefer not to use TCP/IP access like that, and
if you turn on passwords for local, the default do.maintenance cron job
will stop working, because it will not supply a username or password.
In this case, you must alter /etc/cron.d/postgresql to supply the
user and password for the postgres superuser, with the -u and -p options.
It will then be necessary to change the permissions on /etc/cron.d/postgresql
to make it readable by root only.
${HOME}/.pgpass
===============
From release 7.3, the file ${HOME}/.pgpass will be read for a password, if
the file exists and if its permissions exclude access by group or world.
Lines in .pgaccess take the form:
host:port:database:user:password
Lines are read only until the first match of host, port, database and
user is found.
Problems with password authentication
=====================================
1. There is no easy and secure way to automate access when passwords are
in use. It would be good if the postgres super-user (as identified by
Unix on a Unix sockets connection) could bypass the authentication.
2. In general, non-md5 passwords are insecure, because they are held in clear
in pg_shadow. Anyone with create-user privilege can not only alter but
also read them. They ought to be stored with one-way encryption, as
with the Unix password system. Use md5 passwords rather than the older
password methods.
3. The postgres super-user's password can be changed by anyone with
create-user privilege. It ought to be the case that people can
only change their own passwords and that only the super-user can change
other peoples' passwords.
4. If passwords are turned on, the -u option must be supplied to psql. If
it is not, psql merely says "Connection to database 'xxxx' failed.". A
more helpful error message would be desirable.
|
