In some cases, not everybody is allowed to see all the data. Just imagine a bank. Some people might see the entire information about a bank account, while others might be limited to only a subset of the data. In a real-world situation, somebody might not be allowed to read the balance column while somebody else might not see the interest rates of people's loans.

Another example would be that people are allowed to see people's profiles, but not their pictures or some other private information. The question now is this: how can column-level security be used?

To demonstrate this, we will add a column to the existing table belonging to the joe role:

test=> ALTER TABLE t_useful ADD COLUMN name text;  
ALTER TABLE 

The table now consists of two columns. The goal of the example is to ensure that a user can see only one of those columns:

test=> d t_useful 
    Table  "public.t_useful"  
 Column |   Type   | Modifiers 
--------+---------+-----------  
 id     | integer  | 
 name   | text     | 

As a superuser, let's create a user and give it access to the schema containing our table:

test=# CREATE ROLE paul LOGIN;  
CREATE ROLE 
test=# GRANT CONNECT ON DATABASE test TO paul;  
GRANT 
test=# GRANT USAGE ON SCHEMA public TO paul;  
GRANT 

Do not forget to give CONNECT rights to the new guy, because earlier in the chapter, CONNECT was revoked from public. Explicit granting is therefore absolutely necessary to ensure that we can get to the table.

The SELECT permissions can be given to the paul role:

test=# GRANT  SELECT (id)  ON t_useful TO paul;  
GRANT 

This is already enough. It is already possible to connect to the database as the paul user and read the column:

[hs@zenbook ~]$ psql test -U paul 
... 
test=> SELECT id FROM t_useful; 
id 
---- 
(0 rows) 

If we are using column-level permissions, there is an important thing to keep in mind; we should stop using SELECT *, as it will not work anymore:

test=> SELECT * FROM t_useful; 
ERROR:  permission denied for relation t_useful 

* still means all columns, but as there is no way to access all columns, things will error out instantly.