After this theoretical introduction, it is time to actually create users and see how things can be used in a practical example:

test=# CREATE ROLE  bookkeeper NOLOGIN;  
CREATE ROLE 
test=# CREATE ROLE  joe LOGIN;  
CREATE ROLE 
test=# GRANT  bookkeeper TO joe;  
GRANT ROLE 

The first thing done here is that a role called bookkeeper is created.

Note that we don't want people to log in as bookkeeper, so the role is marked as NOLOGIN.

Then, the joe role is created and marked as LOGIN. Finally, the bookkeeper role is assigned to the joe role so that he can do everything a bookkeeper is actually allowed to do.

Once the users are in place, we can test what we have so far:

[hs@zenbook ~]$ psql test -U bookkeeper 
psql: FATAL:  role "bookkeeper" is not permitted to log in 

As expected, the bookkeeper role is not allowed to log in to the system. What happens if the joe role tries to log in?

    [hs@zenbook ~]$ psql test -U joe
    ... 
    test=>  

This will actually work as expected. However, note that Command Prompt has changed. This is just a way for PostgreSQL to show you that you are not logged in as a superuser.

Once a user has been created, it might be necessary to modify it. One thing we might want to change is the password. In PostgreSQL, users are allowed to change their own passwords. Here is how it works:

test=> ALTER  ROLE  joe PASSWORD 'abc';  
ALTER  ROLE 
test=> SELECT current_user; 
 current_user 
--------------  
 joe 
(1 row) 

The ALTER ROLE clause (or ALTER USER) will allow us to change most settings which can be set during user creation. However, there is even more to managing users. In many cases, we want to assign special parameters to a user. The ALTER USER clause gives us the means to do that:

ALTER ROLE { role_specification | ALL } 
     [ IN DATABASE database_name ] 
              SET configuration_parameter { TO | = } { value | DEFAULT } 
ALTER ROLE { role_specification | ALL } 
     [ IN DATABASE database_name ] 
              SET configuration_parameter FROM CURRENT 
ALTER ROLE { role_specification | ALL } 
     [ IN DATABASE database_name ] RESET configuration_parameter 
ALTER ROLE { role_specification | ALL } 
     [ IN DATABASE database_name ] RESET ALL

The syntax is fairly simple and pretty straightforward. To depict why this is really useful, I have added a real-world example. Let's suppose that Joe happens to live on the island of Mauritius. When he logs in, he wants to be in his own time zone, even if his database server is located in Europe:

test=> ALTER  ROLE  joe SET TimeZone = 'UTC-4';  
ALTER  ROLE 
test=> SELECT now(); 
             now 
------------------------------- 
2017-01-09 20:36:48.571584+01 
(1 row) 
 
test=> q 
[hs@zenbook ~]$ psql  test  -U joe 
... 
test=> SELECT now(); 
           now 
------------------------------- 
2017-01-09 23:36:53.357845+04 
(1 row) 

The ALTER ROLE clause will modify the user. As soon as joe reconnects, the time zone will already be set for him.

The important thing here is that this is also possible for some memory parameters, such as work_mem and so on, which have already been covered earlier in this book.