Security - PostgreSQL 9 Administration - page 124

Version 9.0 added a capability to GRANT or REVOKE privileges on all objects of a certain
kind in a specific schema:
GRANT SELECT ON ALL TABLES IN SCHEMA staging TO bob;
You still need to grant the privileges on the schema itself in a separate grant statement.
Creating a new user
In this recipe we show two ways of creating a new database user, one from the command line,
and one using SQL commands.
Getting ready
To create new users, you must either be a superuser or have the createrole
or createuser privilege.
How to do it...
From command line, you run the creatuser command, and answer a few questions:
pguser@hvost:~$ createuser bob
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n
pguser@hvost:~$ createuser tim
Shall the new role be a superuser? (y/n) y
How it works...
The program createuser is just a shallow wrapper around executing SQL against the
database cluster. It connects to database "postgres", asks a question, and then executes SQL
commands for user creation. To create the same users through SQL, you run CREATE USER
SQL command as follows:
CREATE ROLE bob WITH NOSUPERUSER INHERIT NOCREATEROLE CREATEDB LOGIN;
CREATE ROLE tim WITH SUPERUSER;