Analysis of PostgreSQL pg_cmd.conf File

Author: HiLoveS)

Blog: http://www.cnblogs.com/hiloves/

Reprinted please keep this information

　

I recently tried PostgreSQL 9.04 and shared my experiences with pg_mirror.conf configuration. Pg_cmd.conf is the configuration file for client authentication, which defines how to authenticate the client.

The following are common pg_cmd.conf configurations:

# TYPE  DATABASE  USER  CIDR-ADDRESS  METHOD# "local" is for Unix domain socket connections onlylocal    all      all                 ident# IPv4 local connections:host     all      all   127.0.0.1/32  md5# IPv6 local connections:host     all      all   ::1/128       md5

　

TYPE defines a variety of methods to connect to PostgreSQL: "local" uses a local unix socket, "host" uses TCP/IP connection (including SSL and non-SSL ), "host" uses IPv4 in combination with "IPv4 address", and IPv6 in combination with "IPv6 address". "hostssl" can only be connected using SSL TCP/IP, "hostnossl" cannot be connected using SSL TCP/IP.

DATABASE specifies which DATABASE, multiple databases, and DATABASE names are separated by commas. "All" indicates "all" only when there are no other matching entries. If there are other matching entries, it indicates "except this entry ", because "all" has the lowest priority. For example:

local    db1    user1    rejectlocal    all      all        ident

Both of them specify the local access mode, because the previous one specifies the specific database db1, so the next all represents a database other than db1. Similarly, the user's all is also the truth.

USER specifies the database USER (role is the formal name of PostgreSQL ). Multiple users are separated by commas.

CIDR-ADDRESS item local mode is not required, this can be an IPv4 address or IPv6 address, you can define a host or a CIDR block.

METHOD specifies how to handle client authentication. Commonly used include ident, md5, password, trust, and reject.

Ident is the default local authentication method for PostgreSQL in Linux. All operating system users who can log on to the server correctly (Note: Not database users) you can log on to the database without a password. The user ing file is pg_ident.conf, which records the database users that match the operating system users. If an operating system user does not map users to this file, the default ing database user has the same name as the operating system user. For example, an operating system user named user1 on the server and a database user with the same name in the database can directly enter psql after user1 logs on to the operating system, log on to the database as a user1 database user without a password. Many beginners will encounter the "username ident authentication failed" error when logging on to the database with psql-U username. It is clear that the database user has already created a user. This is because ident authentication is used, but there are no operating system users with the same name or corresponding ing users. Solution: 1. Add a ing user in pg_ident.conf; 2. Change the authentication method.

Md5 is a common password authentication method. If you do not use ident, it is best to use md5. The password is sent to the database in the form of md5, which is safe and does not require an operating system user with the same name.

Password is transmitted to the database using a plaintext password. We recommend that you do not use it in the production environment.

Trust can be logged on without a password or ident as long as you know the database username. We recommend that you do not use it in the production environment.

Reject is denied authentication.

　

Locally using psql to log on to the database is in the form of unix socket, with local mode.

Use PGAdmin3 or php to log on to the database, regardless of whether the database is local or not. If it is local (Database address localhost), The CIDR-ADDRESS is 127.0.0.1/32.

　

Example:

1. Allow the local use of PGAdmin3 to log on to the database. The database address is localhost, user user1, and database user1db:

host    user1db    user1    127.0.0.1/32    md5

2. Allow 10.1.1.0 ~ 10.1.1.255 network segment login database:

host    all    all    10.1.1.0/24    md5

3. Trust 192.168.1.10 to log on to the database:

host    all    all    192.168.1.10/32    trust

　

After pg_cmd.conf is modified, use pg_ctl reload to re-read the pg_cmd.conf file. If pg_ctl cannot find the database, use-D /... /pgsql/data/Specify the database directory, or export PGDATA = /... /pgsql/data/import environment variables.

　

In addition, PostgreSQL listens only to local ports by default. Only "tcp 127.0.0.1: 5432 LISTEN" is displayed when netstat-tuln is used ". Modify listen_address = * in postgresql. conf to LISTEN to all ports so that you can remotely log on to the database through TCP/IP. Use netstat-tuln to view "TCP 0.0.0.0: 5432 LISTEN ".