Restricting Role access to Databases

A user or role is created at the PostgreSQL cluster level and it is available or visible inside all the databases. By default, the new role will be able to connect to all existing and new databases and will also be able to create new tables and other objects in those databases. But, it won’t be select data from tables created by other users.

This might not be desirable in many situations and we may need to make sure that a given role can connect to only one database. There is no easy or direct way of achieving that. But, few workarounds are given below.

1. Revoke CONNECT access on a given database from public.

Revoke CONNECT on a given database from public, then grant the CONNECT on that database to the required user.

# REVOKE CONNECT ON DATABASE appdb FROM PUBLIC;

# GRANT CONNECT ON DATABASE appdb TO john;

2. Revoke CONNECT access on template database from public.

Revoke CONNECT on template database template1 from public, and create new databases from this template. Then, the new or existing roles will not have CONNECT access by default on newly created databases.

# REVOKE CONNECT ON DATABASE template1 FROM PUBLIC;

# CREATE DATABASE appdb;