Database Reference
In-Depth Information
If you still want superuser access, then try something like the following:
# TYPE DATABASE USER CIDR-ADDRESS METHOD
local all postgres ident
local all all reject
host all all
0.0.0.0/0
reject
which will prevent connection to the database by any user except the postgres
operating system userid connecting locally to the postgres 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 method "ident" should be replaced by other
authentication methods if a more complex configuration is in use.
Copy the existing
pg_hba.conf
to
pg_hba_access.conf
, so that it can
be replaced again later, if required.
Copy
pg_hba_lockdown.conf
to
pg_hba.conf
Reload the server following the recipe earlier in this chapter.
How it works...
The
pg_hba.conf
is where we specify the host-based authentication rules. We do not specify
the authentications themselves, but just specify which authentication mechanisms will be
used. This is the top-level set of rules for PostgreSQL authentication. The rules are specified
in a file, and applied by the postmaster process when connections are attempted. To prevent
denial of service attacks, the HBA rules never involve database access, so we do not know
whether a user is a superuser or not. As a result, you can lock out
all
users. But note that you
can always re-enable access by editing the file and reloading.
Restricting users to just one session each
If resources need to be closely controlled, you may wish to restrict users so that they can only
connect at most once to the server. The same technique can be used to prevent connections
entirely for that user.
How to do it...
We can restrict users to just one connection by using the following command:
postgres=# ALTER ROLE fred CONNECTION LIMIT 1;
ALTER ROLE
