Postgresql connection introduction and connection FAQs

1. Connect to the correct database in the correct way
If you want to know whether the database is connected to the correct database in the correct way, run the following command to verify the connection:
SELECT inet_server_port (); this command displays the port number listened on by the server.
SELECT current_database (); displays the current database.
SELECT current_user; this command displays the current userid.
SELECT inet_server_addr (); displays the IP address of the server that accepts the connection.
Obviously, users' passwords cannot be accessed using normal SQL statements.
2. Remote database access
For security reasons, many versions of PostgreSQL disable its remote access function by default. So how to enable this function?
To enable network or remote access, add or edit the following content in the postgresql. conf file:
Listen_addresses = '*'
Add the following content in the first line of pg_cmd.conf to allow all users to access all databases by encrypting passwords:
# Type database user CIDR-ADDRESS METHOD
Host all 0.0.0.0/0 md5
The following is a brief description of the above content. The listen_addresses parameter specifies the IP address to listen. In this way, even if there are multiple NICs on each system, it does not matter. In most cases, we accept connections from all NICs, so we can use "*" to indicate all IP addresses.
Pg_cmd.conf contains a set of host-based authentication rules. Each rule will be applied one by one until a matching rule is found, and the authentication will be passed; or the access will be explicitly rejected by the reject method. The rules in the preceding example indicate that all remote connections, no matter who the user is, whether the database is accessed or from which IP address, must be authenticated using an md5 encrypted password.
Type = host indicates a remote connection. Database = all indicates all databases. Other names must be strictly matched unless the prefix is +. This prefix indicates a group role, not a user. We can also define a user table separated by commas, or use @ symbol to include a file that stores the user table. We can also use sameuser, so that the user name matches only when the database name is the same.
User = all indicates all users. Other names must be strictly matched unless the prefix is +. This prefix indicates a group role, not a user. We can also define a user table separated by commas, or use @ symbol to include a file that stores the user table.
The CIDR-ADDRESS is composed of two parts: the IP address/subnet mask. The subnet mask specifies which digits in the IP address represent the network number. Here/0 indicates that the IP address does not represent the network number bit. In this case, all IP addresses match. For example, 192.168.0.0/24 indicates that the first 24 digits are matched, so it matches any IP address in the form of 192.168.0.x. We can also use samenet or samehost.
Note that do not use password settings, so that plaintext passwords are allowed.
Method = trust actually means no authentication is required. Other authentication methods include GSSAPI, SSPI, LDAP, RADIUS, and PAM. In addition, we can also use SSL to connect to PostgreSQL, and then use the client's SSL certificate for authentication.

Error 2:

Psql: cocould not connect to server: Connection refused (0x0000274D/10061)
Is the server running on host "??? "And accepting
TCP/IP connections on port 5432?
This problem is generally caused by www.111cn.net
1. The server fails. Run ps-ef | grep postgres to check whether the PG process exists.
2. Check whether the listening address is correct in cat postgresql. conf | grep listen.
3. The above is okay. The server can be connected, but the client cannot. In this case, you need to check the pg_cmd.conf file.
4. Check the server's iptables and enable the firewall's access port.
 
Error 3:

It is pasted by a user in the group:
Fatal: connection limit exceeded for non-supersers
In fact, this problem is similar to Error 1. The number of connections is close to the number of connections set by the database. Increase the number of connections:
Max_connections is the maximum number of connections allowed by the database. The default value is 100,
Superuser_reserved_connections is the number of connections reserved for super users. The default value is 3.
To modify these two parameters, you must restart the database;
The associated parameters include work_mem. The number of connections * work_mem can get the memory size of the DB. This adjustment depends on the memory size of the server.

Connect to PostgreSQL remotely

 

A: 192.168.1.1 Windows Server 2003
B: 192.168.1.2 Windows XP pro

Machine A installs PostgreSQL. The default port is 5432.
To remotely connect Machine B to the PostgreSQL service of machine.

If host A starts Windows Firewall, add Port 5432 to "add port" in "exceptions.

Modify in postgresql. conf
Listen_addresses = '*'
Port = 5432

Add
Host all 192.168.1.2/32 trust

Use pgAdminIII on Machine B to connect to machine A 192.168.1.1