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.


