Using the CONNECT privilege

One of the cool new features in PostgreSQL in the connect privilege. The CONNECT privilege enables you to grant connection rights from a user to a database, avoiding the need to configure user to database access in the pg_hba.conf.
The information in the pg_hba.conf could exclusively be used to administer the authentication (Are you allowed on my server?) process and the CONNECT privilege could be used for authorization (What are you allowed to do on my server?)

Let us look at the following scenario:

Imagine you want to enable different users on your network to access different databases on your server. Configuring every user for every permitted database in pg_hba.conf would be time consuming and less administer-friendly. The process could be simplified by allowing a certain network (or subnet) to authenticate and handle the database connection by CONNECT privilege.

In pg_hba.conf:
# Allow all network users to connect to by server.
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host all all 192.168.1.0/24 password

Then secure the databases by:
-- Revoke connection right from all users to main database.
-- Dont worry, the superuser can still connect.
REVOKE CONNECT ON DATABASE postgres FROM PUBLIC;

-- create a new user called joe
CREATE ROLE joe LOGIN;

-- create a new database owned by joe.
CREATE DATABASE customers OWNER joe ENCODING UTF8

-- Revoke connection from everybody on customers database
REVOKE CONNECT ON DATABASE customers FROM PUBLIC;

-- Authorize Joe exclusively to connect to customers database
GRANT CONNECT ON DATABASE customers TO joe;

This way only Joe or anyone in that role can connect customers database.

This entry was posted on Friday, September 22nd, 2006 at 10:36 am and is filed under PostgreSQL. You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.

Comments are closed.