- 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 ROLEpostgres=# create database fred owner fred;CREATE DATABASE
- 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 ...