Managing Users and Connections

PostgreSQL is a complex system that includes users, databases, and data. In order to be able to interact with a database in the cluster, you need to have at least one user. By default, when installing a new cluster, a single administrator user (named postgres) is created. While it is possible to handle all the connections, applications, and databases with that single administrative user, it is much better to create different users with different properties and privileges, as well as login credentials, for every specific task.

PostgreSQL provides a very rich user-management structure, and single users can be grouped into a variety of different groups at the same time. Moreover, groups can be nested within other groups, so that you can have a very accurate representation of your account model. Thanks to this accurate representation, and thanks to the...

Introduction to users and groups

In order to connect interactively or via an application to a PostgreSQL database, you need to have login credentials. In particular, a database user, a user who is allowed to connect to that specific database, must exist.

Database users are somewhat similar to operating system users: they have a username and a password (usually encrypted) and are known to the PostgreSQL cluster. Similarly to operating system users, database users can be grouped into user groups in order to ease the massive administration of users.

In SQL, and therefore even in PostgreSQL, both concepts of a single user account and a group of accounts are encompassed by the concept of a role.

A role can be a single account, a group of accounts, or even both depending on how you configure it; however, in order to ease management, a role should express one and only one concept at a time: that is, it should be either a single user or a single group, but not both.

While a role can be used simultaneously...

Managing roles

Roles can be managed by means of three main SQL statements: CREATE ROLE to create a role from scratch, ALTER ROLE to change some role properties (for example, the login password), and DROP ROLE to remove an existing role.

In order to use the SQL statements to create new roles and then manage them, it is necessary to connect to a database in the cluster. The superuser role postgres can be used to that aim, at least initially; such a role is created when the database cluster is initialized. Using the postgres role and a template database is the most common way to create your initial roles.

PostgreSQL ships with a set of shell scripts that can be used to create, modify, and delete roles without connecting directly to the cluster. Under the hood, those scripts connect to the template database and perform the same SQL commands found in this section.

A role is identified by a string that represents the role name, or better, the account name of that role. Such a name must be...

Managing incoming connections at the role level

When a new connection is established to a cluster, PostgreSQL validates the incoming request at the role level. The fact that the role has the LOGIN property is not enough for it to open a new connection to any database within the cluster. This is because PostgreSQL checks the incoming connection request against a kind of firewall table, formerly know as host-based access, that is defined within the pg_hba.conf file.

If the table states that the role can open the connection to the specified database, the connection is granted (assuming it has the LOGIN property); otherwise, it is rejected.

Every time you modify the pg_hba.conf file, you need to instruct the cluster to reload the new rules via a HUP signal or by means of a reload command in pg_ctl. Therefore, the usual workflow when dealing with pg_hba.conf is similar to the following:

$ $EDITOR $PGDATA/pg_hba.conf
... modify the file as you wish ...

$ sudo -u postgres pg_ctl reload -D $PGDATA...

Summary

Roles are a powerful tool to represent both single users and a group of users. When a database connection attempt is made, PostgreSQL processes the connection credential information through the host-based access control so that it can immediately establish or reject the connection depending on firewall-like rules. Moreover, single users and groups can have other limitations to the number of connections they can open against a database or against the whole cluster.

In this chapter, you have seen how to create and manage roles, as well as how to allow single roles to connect to the cluster and to specific databases. In Chapter 10, Users, Roles, and Database Security, you will see how to deal with the security properties of users and groups, but before you proceed further, you need to know how PostgreSQL objects can be created and managed.

In the following chapter, you will learn how to interact with the PostgreSQL database using SQL statements.

References

• CREATE ROLE statement official documentation: https://www.postgresql.org/docs/12/sql-createrole.html
• DROP ROLE statement official documentation: https://www.postgresql.org/docs/12/sql-droprole.html
• PostgreSQL pg_roles catalog details: https://www.postgresql.org/docs/12/view-pg-roles.html
• PostgreSQL pg_authid catalog details: https://www.postgresql.org/docs/12/catalog-pg-authid.html
• PostgreSQL host-based access rule details: https://www.postgresql.org/docs/12/auth-pg-hba-conf.html