After configuring users at the instance level, it is possible to dig deeper and see what can be done at the database level. The first major question that arises is this: we explicitly allowed Joe to log in to the database instance, but who or what allowed Joe to actually connect to one of the databases? Maybe you don't want Joe to access all the databases in your system. Restricting access to certain databases is exactly what we can achieve at this level.

For databases, the following permissions can be set using a GRANT clause:

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] 
     | ALL [ PRIVILEGES ] } 
    ON DATABASE database_name [, ...] 
    TO role_specification [, ...] [ WITH GRANT OPTION ]  

There are two major permissions on the database level that deserve close attention:

• CREATE: This allows somebody to create a schema inside the database. Note that a CREATE clause does not allow for the creation of tables; it is about schemas. In PostgreSQL, a table resides inside a schema, so you have to get to the schema level first in order to be able to create a table.
• CONNECT: This allows somebody to connect to a database.

The question now is this: nobody has explicitly assigned any CONNECT permissions to the joe role, so where do those permissions actually come from? The answer is this: there is a thing called public, which is similar to the Unix world. If the world is allowed to do something, so is joe, who is part of the general public.

The main thing is that public is not a role in the sense that it can be dropped and renamed. We can simply see it as the equivalent of everybody on the system.

So, to ensure that not everybody can connect to any database at any time, CONNECT may have to be revoked from the general public. To do so, we can connect as a superuser and fix the problem:

[hs@zenbook ~]$ psql  test  -U postgres 
... 
test=# REVOKE ALL ON DATABASE test FROM public;  
REVOKE 
test=# q 
[hs@zenbook ~]$ psql test -U joe 
psql:  FATAL:  permission denied for database "test"  
DETAIL:  User does not have CONNECT privilege. 

As we can see, the joe role is not allowed to connect anymore. At this point, only superusers have access to test.

In general, it is a good idea to revoke permissions from the postgres database even before other databases are created. The idea behind this concept is that those permissions won't be in all those newly created databases anymore. If somebody needs access to a certain database, the rights have to be explicitly granted. The rights are not automatically there anymore.

If we want to allow the joe role to connect to the test database, try the following line as a superuser:

[hs@zenbook ~]$ psql test -U postgres 
... 
test=# GRANT CONNECT ON DATABASE test TO bookkeeper;  
GRANT 
test=# q 
[hs@zenbook ~]$ psql test -U joe 
...  
test=> 

There are two choices here:

• We can allow the joe role directly, so that only the joe role will be able to connect.
• Alternatively, we can grant permissions to the bookkeeper role. Remember, the joe role will inherit all the permissions from the bookkeeper role, so if we want all accountants to be able to connect to the database, assigning permissions to the bookkeeper role seems to be an attractive idea.

It is not risky if we grant permissions to the bookkeeper role, because the role is not allowed to log in to the instance in the first place, so it purely serves as a source of permissions.