Setting up client connections PostgreSQL does not require a password

In normal work, sometimes need remote connection PostgreSQL database to do some maintenance, such as remote backup, etc. if the backup script is written in the remote machine, the backup will pop up the password input prompt, then the script can not be executed in the background, here summarizes several ways not to pop the password input hint.

--Test environment
Target library ip:192.168.1.25/5432; database: Mydb; user name: Postgres
Client ip:192.168.1.26

--Connect to database in 192.168.1.26 Mydb , popup password hint
[Email protected]> psql-h 192.168.1.25-p 5432 Mydb postgres
Password for user postgres:

Method One: Set the environment variable Pgpassword

Pgpassword is the PostgreSQL system environment variable, and after the client has set this, the password will be preferred when the client connects to the remote database.

--Test
[Email protected]> Export Pgpassword=mypassword

[Email protected]> psql-h 192.168.1.25-p 5432 Mydb postgres
Psql (8.4.4)
Type ' help ' for help.
Mydb=> \q

Note: Set environment variable Pgpassword, connect database no longer popup password input prompt. However, this approach is not recommended for security reasons.

Method Two: Set the. Pgpass Password file

By creating a hidden file in the client/home/postgres directory, you can avoid prompting for password entry when connecting to the database. Pgpass.

--Create a password file. Pgpass (on client)      
vi/home/postgres/.pgpass        
--format     
Hostname:port: database:username:password                              
--Example     
192.168.1.25:5432:mydb:postgres:mypassword                                     
--Permissions            
Chmod .pgpass                  

--Connection test
[Email protected]> psql-h 192.168.1.25-p 5432 Mydb postgres

Psql (8.4.4)
Type ' help ' for help.
Mydb=>

Note: After the/home/postgres directory has created the password file. pgpass file, and the connection information is properly configured, the client connects the data with the. pgass file and uses the password of the matching record so that it does not step out of the password input hint, which is more secure than the method one. It is recommended to use the Create. Pgpass file method.

Method Three: Modify server-side pg_hba.conf

Modify the authentication file $PGDATA/pg_hba.conf, add the following line, and reload make the configuration take effect immediately.
Host Mydb Postgres 192.168.0.0/24 Trust

[[email protected]]$ pg_ctl reload-d $PGDATA/service PostgreSQL reload
server signaled

-Configuration of server-side pg_hba.conf
# IPv4 Local connections:
host    all     & nbsp;       all                  127.0.0.1/32                Trust
host    mydb        postgres        192.168.0.0/24           Trust
host    all             all                   0.0.0.0/0                      MD5

--The client connects the test again
[Email protected]> psql-h 192.168.1.25-p 5432 Mydb postgres

Psql (8.4.4)
Type ' help ' for help.

Mydb=> \q

Note: After modifying the server side pg_hba.conf and reload, the password input prompt is no longer displayed.

Refer to the official documentation:

The. Pgpass or pgpassfile referenced in the user's home directory is a file that can contain a password. If the connection requires a password (and no other method is used to declare the password), it can be used. On Microsoft Windows, the file name is%appdata%\postgresql\pgpass.conf (here%appdata% refers to the application Data subdirectory in the User Configuration).

This file should have a format line such as the following:

hostname:port:database:username:password

The first four fields each can be a literal value, or *, it matches everything. The password field for the first password line that matches the current connection parameter will be used. (therefore, if you use wildcards, you should put the most specific records in front.) If the record contains: or \, you should use \ Escape. A localhost hostname matches the host (TCP) and local (Unix domain sockets) from the local computer.

The permissions of the. Pgpass must not be allowed to be accessed by any global or group of users, and we can do this with the command chmod 0600 ~/.pgpass. If the permissions are more loose than this, the file will be ignored. (However, there is currently no permission to check this file on Microsoft Windows.) ）

Setting up client connections PostgreSQL does not require a password