We can all agree that hardcoding your password is a bad idea. This recipe shows you how to keep your password in a secure password file.
Not all database users need passwords; some databases use other means of authentication. Don't perform this step unless you know you will be using password authentication, and you know your password.
First, remove the hardcoded password from where you set it previously. Completely remove the password = xxxx text from the connection string in a program. Otherwise, when you test the password file, the hardcoded setting will override the details you are about to place in the file. Keeping the password hardcoded and in the password file is not any better. Using PGPASSWORD is not recommended either, so remove that also.
If you think someone may have seen the password, change your password before placing it in the secure password file.
A password file contains the usual five fields that we require when connecting, as shown here:
host:port:dbname:user:passwordChange this to the following:
myhost:5432:postgres:sriggs:moresecureThe password file is located using an environment variable named PGPASSFILE. If PGPASSFILE is not set, a default filename and location must be searched for, as follows:
- On *nix systems, look for
~/.pgpass - On Windows systems, look for
%APPDATA%\postgresql\pgpass.conf, where%APPDATA%is the application data subdirectory in the path (for me, that would beC:\)
Note
Don't forget to set the file permissions on the file, so that security is maintained. File permissions are not enforced on Windows, though the default location is secure. On *nix systems, you must issue the following command: chmod 0600 ~/.pgpass.
If you forget to do this, the PostgreSQL client will ignore the .pgpass file. While the psql tool will issue a clear warning, many other clients will just fail silently, so don't forget!
Many people name the password file .pgpass, whether or not they are on Windows, so don't get confused if they do this.
The password file can contain multiple lines. Each line is matched against the requested host:port:dbname:user combination until we find a line that matches. Then, we use that password.
Each item can be a literal value or *, a wildcard that matches anything. There is no support for partial matching. With appropriate permissions, a user can potentially connect to any database. Using the wildcard in the dbname and port fields makes sense, but it is less useful in other fields. The following are a few examples of wildcards:
myhost:5432:*:sriggs:moresecurepwmyhost:5432:perf:hannu:okpwmyhost:*:perf:gianni:sicurissimo
This looks like a good improvement if you have a few database servers. If you have many different database servers, you may want to think about using a connection service file instead (see the Using a connection service file recipe), or perhaps even storing details on a Lightweight Directory Access Protocol (LDAP) server.