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.