Skip to content
Announcing BigAnimal: Fully managed PostgreSQL in the Cloud
Postgres Build 2021: 30 Nov to 1 Dec
Contact usDocsPlans

Limiting Postgres Connections

Bruce Momjian2/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.

Bruce Momjian is a co-founder of the PostgreSQL Global Development Group, and has worked on PostgreSQL since 1996 as a committer and community leader. He is a frequent speaker and Postgres evangelist and travels worldwide appearing at conferences to help educate the community on the business value o ...