Once we are done configuring the database level, it makes sense to take a look at the schema level.

Before actually taking a look at the schema, let's run a small test:

test=> CREATE DATABASE test; 
ERROR:  permission denied to create database test=>  
CREATE USER  xy; 
ERROR:  permission denied to create role test=>  
CREATE SCHEMA sales; 
ERROR:  permission denied for database test 

As we can see, Joe is having a bad day and nothing but connecting to the database is allowed.

However, there is a small exception, and it comes as a surprise to many people:

test=> CREATE TABLE t_broken (id int);  
CREATE TABLE 
test=> d 
          List  of relations 
Schema  |   Name   | Type   | Owner 
--------+----------+--------+-------  
 public | t_broken | table  | joe 
(1 rows) 

By default, public is allowed to work with the public schema, which is always around. If we are seriously interested in securing our database, make sure that this problem is taken care of. Otherwise, normal users will potentially spam your public schema with all kinds of tables and the entire setup might suffer. You should also keep in mind that if somebody is allowed to create an object, that person is also its owner. Ownership means that all permissions are automatically available to the creator, including the destruction of the object.

To take those permissions away from public, run the following line as a superuser:

test=# REVOKE ALL ON SCHEMA public FROM public;  
REVOKE 

From now on, nobody can put things into your public schema without the correct permissions any more. The next listing is proof of that:

[hs@zenbook ~]$ psql test -U joe 
... 
test=> CREATE TABLE  t_data (id int); 
ERROR:  no schema has been selected to create in 
LINE  1: CREATE TABLE t_data (id int); 

As we can see, the command will fail. The important thing here is the error message that will be displayed; PostgreSQL does not know where to put these tables. By default, it will try to put the table into one of the following schema:

test=> SHOW search_path ; 
 search_path 
-----------------  
 "$user", public 
(1 row) 

As there is no schema called joe, it is not an option, so PostgreSQL will try the public schema. As there are no permissions, it will complain that it does not know where to put the table.

If the table is explicitly prefixed, the situation will change instantly:

test=> CREATE TABLE  public.t_data (id int);  
ERROR:  permission denied for schema public  
LINE  1: CREATE TABLE  public.t_data (id int); 

In this case, we will get the error message that you expect. PostgreSQL denies access to the public schema.

The next logical question now is this: which permissions can be set at the schema level to give some more power to the joe role:

GRANT  { { CREATE | USAGE  } [, ...]  | ALL [ PRIVILEGES ] }  
  ON SCHEMA schema_name [, ...] 
TO role_specification [, ...]  [ WITH  GRANT  OPTION ] 

CREATE means that somebody can put objects into a schema. USAGE means that somebody is allowed to enter the schema. Note that entering the schema does not mean that something inside the schema can actually be used; those permissions have not been defined yet. This just means that the user can see the system catalog for this schema.

To allow the joe role to access the table it has created previously, the following line will be necessary (executed as a superuser):

test=# GRANT USAGE ON SCHEMA public TO bookkeeper;  
GRANT 

The joe role is now able to read its table as expected:

[hs@zenbook ~]$ psql test -U joe  
test=> SELECT count(*) FROM t_broken;  
 count 
------- 
     0 
(1 row) 

The joe role is also able to add and modify rows, because it happens to be the owner of the table. However, although it can do quite a lot of things already, the joe role is not yet almighty. Consider the following statement:

test=> ALTER TABLE t_broken RENAME TO t_useful;  
ERROR:  permission denied for schema public 

Let's take a closer look at the actual error message. As we can see, the message complains about the permissions on the schema, not about the permissions on the table itself (remember, the joe role owns the table). To fix the problem, it has to be tackled on the schema level and not on the table level. Run the following line as a superuser:

test=# GRANT CREATE ON SCHEMA public TO bookkeeper;  
GRANT 

The joe role can now change the name of its table to a more useful name:

[hs@zenbook ~]$ psql test -U joe 
test=> ALTER TABLE t_broken RENAME TO t_useful;  
ALTER TABLE 

Keep in mind that this is necessary if DDLs are used. In my daily work as a PostgreSQL support service provider, I have seen a couple of issues where this turned out to be a problem.