How to do it…

1. We can create a database for a specific user with some ease. From the command line, as a superuser, these actions would be as follows:

postgres=# create user fred;
CREATE ROLE
postgres=# create database fred owner fred;
CREATE DATABASE

1. As the database owners, users have login privileges, so they can connect to any database by default. There is a command named ALTER DEFAULT PRIVILEGES, however, this does not currently apply to databases, tablespaces, or languages. The ALTER DEFAULT PRIVILEGES command also currently applies only to roles (that is, users) that already exist.

So, we need to revoke the privilege to connect to our new database from everybody except the designated user. There isn't a REVOKE ... FROM PUBLIC EXCEPT command. Therefore, we need to revoke everything and then just re-grant everything we need, all in one transaction, such as in the following:

postgres=# BEGIN;
BEGIN
postgres=# REVOKE connect ON DATABASE  fred FROM public;
REVOKE
postgres=# GRANT connect ON DATABASE fred TO fred;
GRANT
postgres=# COMMIT;
COMMIT
postgres=# create user bob;
CREATE ROLE

1. Then, try to connect as bob to the fred database:

os $ psql -U bob fred
psql: FATAL:  permission denied for database "fred"
DETAIL:  User does not have CONNECT privilege.

This is exactly what we wanted.