Connections can be prevented in a number of ways, as follows:
- Pause and resume the session pool. See the Setting up a connection pool recipe later in this chapter on controlling connection pools.
- Stop the server! See the Stopping the server safely and quickly and Stopping the server in an emergency recipes, but this is not recommended.
- Restrict the connections for a specific database to zero, by setting the connection limit to zero:
ALTER DATABASE foo_db CONNECTION LIMIT 0;
- This will limit normal users from connecting to that database, though it will still allow superuser connections.
- Restrict the connections for a specific user to zero by setting the connection limit to zero (see the Restricting users to only one session each recipe):
ALTER USER foo CONNECTION LIMIT 0;
- This will limit normal users from connecting to that database, but it will still allow connections if the user is a superuser, so luckily you cannot shut yourself out accidentally.
- Change the host-based authentication (HBA) file to refuse all incoming connections, and then reload the server:
- Create a new file named pg_hba_lockdown.conf, and add the following two lines to the file. This puts in place rules that will completely lock down the server, including superusers. You should have no doubt that this is a serious and drastic action:
# TYPE DATABASE USER ADDRESS METHOD
local all all reject
host all all 0.0.0.0/0 reject
If you still want superuser access, then try something like the following:
# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer
local all all reject
host all all 0.0.0.0/0 reject
This will prevent connections to the database by any user except the postgres operating system user ID, which connects locally to any database. Be careful not to confuse the second and third columns—the second column is the database and the third column is the username. It's worth keeping the header line just for that reason. The peer method should be replaced by other authentication methods if a more complex configuration is in use.
- Copy the existing pg_hba.conf file to pg_hba_access.conf so that it can be replaced later, if required
- Copy pg_hba_lockdown.conf to pg_hba.conf
- Reload the server following the recipe earlier in this chapter