Database Reference
In-Depth Information
Recent versions of PostgreSQL no longer use the terms
users
and
groups
. You will still see these terms bandied about on discussion
boards; just know that they mean login roles and group roles re‐
spectively. For backward compatibility,
CREATE USER
and
CREATE
GROUP
still work in current version, but shun them and use
CREATE
ROLE
instead.
Creating Login Roles
When you initialize the data cluster during setup, PostgreSQL creates a single role for
you with the name
postgres
. (PostgreSQL also creates a namesake database called
postgres
.) You can bypass the password setting by mapping an OS root user to the new
role. After you've installed PostgreSQL, before you do anything else, you should log in
as
postgres
using psql or pgAdmin and create other roles. pgAdmin has a graphical
section for creating user roles, but if you want to create one using SQL, execute an SQL
command like the one shown in
Example 2-4
.
Example 2-4. Creating login roles
CREATE
ROLE
leo
LOGIN
PASSWORD
'king'
CREATEDB
VALID
UNTIL
'infinity'
;
The
VALID
line is optional and specifies when the role should expire and lose its privi‐
leges; the default is
infinity
, which means the role never expires. The
CREATEDB
modi‐
fier grants database creation rights to the new role.
To create a user with superuser rights, do so as shown in
Example 2-5
. Naturally, you
can create a superuser only if you are a superuser yourself.
Example 2-5. Creating superuser roles
CREATE
ROLE
regina
LOGIN
PASSWORD
'queen'
SUPERUSER
VALID
UNTIL
'2020-1-1 00:00'
;
We don't really want our queen to reign forever, so we added an abdication date.
Creating Group Roles
Group roles generally have no login rights but serve as containers for other roles. This
is merely a best-practice suggestion. Nothing stops you from creating a role that can
both log in and contain other roles.
Create a group role through the following SQL:
CREATE
ROLE
royalty
INHERIT
;
Note the use of term
INHERIT
. This means that any member of
royalty
will automati‐
cally have rights granted to the
royalty
role, except for superuser rights. For security,
PostgreSQL never passes on superuser rights.
