A brief analysis of PostgreSQL's pg_hba.conf file

Recently tried PostgreSQL 9.04 to share some of the pg_hba.conf configuration tips.

Pg_hba.conf is a client authentication profile that defines how clients are authenticated.

The following are common pg_hba.conf configurations:

12345678910

# type  database  user  cidr-address  METHOD    # "local" is for Unix Domain socket connections only local    all       all                  Ident    # IPv4 Local Connections: host     all      all    127.0.0.1/32  MD5    # IPv6 local connections: host     all       all  ::1/128       MD5

　

The type defines a variety of ways to connect to PostgreSQL, namely:

"Local" uses native UNIX sockets,

"Host" uses TCP/IP connections (both SSL and non-SSL),

"Host" combined with "IPV4 address" using IPV4 mode,

Combined with "IPV6 address" The IPv6 method is used,

"Hostssl" can only use SSL TCP/IP connections,

"Hostnossl" Cannot use SSL TCP/IP connections.

database specifies which databases, multiple databases, and the names of the libraries are separated by commas.

"All" represents "all" only if there are no other qualifying entries, and if there are other entries that are "other than this," because "all" has the lowest priority.

The following example:

12	local    db1    user1    rejectlocal    all      all        ident

Both of these are specified local access, because the previous article specified a specific database DB1,

So all of the latter represents the database except for DB1, and the same is true for all users.

user Specifies which database users (PostgreSQL is formally called roles, role). Multiple users are separated by commas.

Cidr-address The local method does not have to be filled out, the item can be an IPV4 address or IPV6 address, you can define a host or a network segment.

METHOD specifies how the client's authentication is handled. Commonly used are ident,md5,password,trust,reject.

Ident is the default local authentication method under the Linux PostgreSQL, the user who can log on to the server correctly (note: not a database user) can use this user mapping database users do not need a password to log in to the database.

The user mapping file is pg_ident.conf, which records database users that match the operating system user, and if an operating system user does not have a mapped user in this file, the default mapping database user has the same name as the operating system user.

For example, the server is known as User1 operating system users, and the database also has the same name of the database user, User1 log on to the operating system can be directly entered Psql, to User1 database users to log into the database and do not require a password.

Many beginners will encounter psql-u username login database But "Username ident authentication failed" error, clearly the database user has createuser.

The reason for this is that the Ident authentication method is used, but there is no operating system user with the same name or no corresponding mapping user.

Solution: 1, add the mapping user in pg_ident.conf;

2, change the authentication method.

MD5 is a common method of password authentication, if you do not use ident, it is best to use MD5.

The password is transmitted to the database in the form of MD5, which is more secure and does not require an operating system user with the same name.

Password is sent to the database in plaintext and is not recommended for use in a production environment.

Trust does not require a password or ident to log in as long as the database username is known, and is not recommended for use in a production environment.

Reject is a denial of certification.

　

Local use of the Psql login database, in the form of UNIX sockets, in the local way.

Use PGAdmin3 or PHP to log in to the database, regardless of whether the local is TCP/IP, the host method is attached. If it is local (database address localhost),

The cidr-address is 127.0.0.1/32.

　

Cases:

Allow local use of PGADMIN3 login database, database address localhost, user user1, database user1db:

host    user1db    user1    127.0.0.1/32    md5

Allow the 10.1.1.0~10.1.1.255 network segment to log in to the database:

host    all    all    10.1.1.0/24    md5

Trust 192.168.1.10 Login Database:

host    all    all    192.168.1.10/32    trust

　

pg_hba.conf modified, use Pg_ctl reload to reread the pg_hba.conf file, if Pg_ctl cannot find the database, specify the database directory with-D/.../pgsql/data/, or export pgdata=/.../ pgsql/data/Import Environment variables.

　

Another: PostgreSQL only listens to the local port by default, with NETSTAT-TULN only see "TCP 127.0.0.1:5432 LISTEN". Modify the listen_address=* in postgresql.conf, listen to all ports so that the remote can log in to the database via TCP/IP, and NETSTAT-TULN will see "TCP 0.0.0.0:5432 LISTEN".