Limiting Postgres Connections

February 24, 2012

Friday, February 24, 2012

During recent training, I realized there five ways to control who can access Postgres, and it isn't totally clear in the documentation how they fit together. There are basically a series of gates that users must pass through to connect to a Postgres database. I thought I would list them here, in the order they are applied:

  • listen_addresses: This controls which network interfaces Postgres will listen on for connections. This default so "localhost" on most Postgres distributions, including the source install. However, the click-through installers default to "*", meaning they listen on all interfaces by default.
  • Host-Based Authentication (pg_hba.conf): The pg_hba.conf file controls connection access to the server. It can filter on:
    • ssl mode
    • database name
    • user name
    • IP address
  • Authentication: Postgres supports eleven authentication methods; hopefully one of them matches your site-wide standard.
  • Database CONNECTION LIMIT: ALTER DATABASE allows you to set the number of people who are allowed to connect to a specific database. Setting it to zero disallows new database connections.
  • Role CONNECTION LIMIT: ALTER USER allows the same control as ALTER DATABASE, but for roles.

Once all of these gates have been passed, authorization is now controlled by the GRANT and REVOKE commands.

Share this