So far, we have configured bind addresses and we have told PostgreSQL which means of authentication to use for which IP ranges. Up to now, the configuration was purely network-related.

In the next step, we can shift our attention to permissions at the instance level. The most important thing to know is that users in PostgreSQL exist at the instance level. If we create a user, it is not just visible inside one database; it can be seen by all the databases. A user might have permissions to access just a single database, but basically users are created at the instance level.

To those of you who are new to PostgreSQL, there is one more thing you should keep in mind: users and roles are the same thing. CREATE ROLE and CREATE USER clauses have different default values (literally, the only difference is that roles do not get the LOGIN attribute by default), but at the end of the day, users and roles are the same. Therefore, CREATE ROLE and CREATE USER clauses support the very same syntax:

test=# h CREATE USER
Command: CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

Let's discuss those syntax elements one by one. The first thing we see is that a user can be a superuser or a normal user. If somebody is marked as a SUPERUSER , there are no longer any restrictions that a normal user has to face. A SUPERUSER can drop objects (databases and so on) as they wish.

The next important thing is that it takes permissions on the instance level to create a new database.

The rule is this: the creator is always automatically the owner of an object (unless specified otherwise, as can be done with the CREATE DATABASE clause). The beauty is that object owners can also drop an object again.

The next important thing is the INHERIT/NOINHERIT clause. If the INHERIT clause is set (which is the default value), a user can inherit permissions from some other user. Using inherited permissions allows us to use roles, which is as a good way to abstract permissions. For example, we can create the role of bookkeeper and make many other roles inherit from bookkeeper. The idea is that we only have to tell PostgreSQL once what a bookkeeper is allowed to do, even if we have many people working in accounting.

The LOGIN/NOLOGIN clause defines whether a role is allowed to log in to the instance.

At this point, we are trying to make it into the instance, which is basically the gate to all the databases inside the instance. Let's get back to our example: the bookkeeper might be marked as NOLOGIN because we want people to log in with their real name. All your accountants (say, Joe and Jane) might be marked as the LOGIN clause but can inherit all the permissions from the bookkeeper role. A structure such as this makes it easy to assure that all bookkeepers will have the same permissions while ensuring their individual activity is operated and logged under their separate identities.

If we are planning to run PostgreSQL with streaming replication, we can do all the transaction log streaming as a superuser. However, doing that is not recommended from a security point of view. As an assurance, that we don't have to be a superuser to stream xlog, PostgreSQL allows us to give replication rights to a normal user, which can then be used to do streaming. It is common practice to create a special user just for the purpose of managing streaming.

As we will see later in this chapter, PostgreSQL provides a feature called row-level security. The idea is that we can exclude rows from the scope of a user. If a user is explicitly supposed to bypass RLS, set this value to BYPASSRLS. The default value is NOBYPASSRLS.

Sometimes, it makes sense to restrict the number of connections allowed for a user. CONNECTION LIMIT allows us to do exactly that. Note that, overall, there can never be more connections than defined in the postgresql.conf file (max_connections). However, we can always restrict certain users to a lower value.

By default, PostgreSQL will store passwords in the system table encrypted, which is a good default behavior. However, suppose you are doing a training course and ten students are attending and everybody is connected to your box. You can be 100% certain that one of those people will forget his or her password once in a while. As your setup is not security critical, you might decide to store the password in plain text so that you can easily look it up and give it to a student. This feature might also come in handy if you are testing software.

Often, we already know that somebody will leave the organization fairly soon. The VALID UNTIL clause allows us to automatically lock out a specific user if his or her account has expired.

The IN ROLE clause lists one or more existing roles to which the new role will be immediately added as a new member. It helps to avoid additional manual steps. An alternative to IN ROLE is IN GROUP.

The ROLE clause will define roles that are automatically added as members of the new role.

The ADMIN clause is the same as the ROLE clause but adds WITH ADMIN OPTION.

Finally, we can use the SYSID clause to set a specific ID for the user (similar to what some Unix administrators do for usernames at the operating system level).