Database Administration - PostgreSQL: Up and Running - page 26

Add roles to the group role with an SQL statement like:
GRANT royalty TO leo ;
GRANT royalty TO regina ;
Inheriting rights from group roles
One quirk (or convenience) in PostgreSQL is the ability to specify that a group role not
pass its rights to member roles. To avoid having to remember the default value, you
should always append the INHERIT keyword if you want members to inherit the rights
of the parent role, and NOINHERIT if you don't want them to inherit the rights of the
parent role.
Some rights can't be inherited. For example, although you can create a group role that
you mark as superuser, this doesn't make its member roles superusers; however, those
users can “impersonate” their parent role through the use of SET ROLE , thereby gaining
superuser rights for the duration of the session. For instance, a member of the royal
ty group can take on that role through:
SET ROLE royalty ;
Keep in mind that this is per-connection session and not a permanent delegation of
rights. To assign noninheritable rights to member roles, you have to do it on a member-
by-member basis. This is to guard against inadvertently granting superuser rights to a
bunch of roles.
A more powerful impersonation than SET ROLE some_role is SET SESSION AUTHORI
ZATION some_role . The main differences between SET ROLE and SET SESSION AUTHOR
IZATION are:
• Only superusers can execute SET SESSION AUTHORIZATION , and it allows them to
impersonate any user regardless of role membership.
• SET SESSION AUTHORIZATION changes the values of the current_user and ses
sion_user variables to those of the user being impersonated. SET ROLE changes
only the current_user variable.
• Because both the current_user and session_user are changed by SET SESSION
AUTHORIZATION , subsequent SET role commands are limited to those allowed by
the user being impersonated. After SET ROLE , roles can be set to any role that the
original user has rights to impersonate.
Database Creation
The bare-bones SQL to create a database is:
CREATE DATABASE mydb ;