PostgreSQL Auto-Enter password

In the shell command line, when you use Postgresql-client to connect to the PostgreSQL database, you enter the password every time. If you want to write Shell Script, do some kind of backup-like automation management, each time you have to manually enter the password is a very uncomfortable thing, and did not realize the true meaning of automation.

How to connect the PostgreSQL database without entering the password manually, the official use of the password files (Password file) provides a solution:

Unix/linux create a. pgpass hidden file in the user's home directory or create an environment variable Pgpassfile reference a file containing password information (the file contents are the same as the. pgpass file, where the filename is arbitrary, hidden), Windows corresponds to the% The appdata%\postgresql\pgpass.conf file (%appdata% is a application Data subdirectory within the user configuration).

This file records all the information needed to connect to the database, in the following data format, one per line of record information:

Hostname:port:database:username:password

There can be comments in the file, and the comment symbol is #.

The first four fields can be either deterministic literal values, or you can use wildcard characters * to match all.

When connecting to a database, the system automatically traverses the file from front to back, using the records that match first, so when you use a wildcard * in a file, you should prioritize the most specific information at the top of the file.

If the record information contains: or \ These two symbols, you need to escape it, is to precede the character with the escape symbol \.

In the Unix/linux system. Pgpass file permissions can only be opened to users themselves, not to user groups and others, that is, the. pgpass file is "unreadable, non-writable, non-executable" for user groups and others, and is usually given a. pgpass file permission 600, with the following command:

chmod 0600 ~/.pgpass

If any of the permissions are open to the user group or others, the file is ignored.

The file permissions referenced by the environment variable Pgpassfile also meet this requirement, otherwise they will be ignored as well.

In Windows, it is assumed that the file resides in a secure directory, and that all of its permissions are not specifically checked.

At this point, the database corresponding to the. pgpass file is set to complete, the future connection to the database will not need to manually enter the password, Shell Script can also achieve a true sense of automation management.

10.150. 10.150 5432 -u postgres-d dbname  --Find matching information in the. pgpass file, and if not, still need to enter the password manually

PostgreSQL Auto-Enter password