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 for security and privilege isolation 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 fact that every user and group can be assigned different properties...

Technical requirements

The chapter examples can be run on the standalone Docker image that you can find in the book’s GitHub repository: https://github.com/PacktPublishing/Learn-PostgreSQL-Second-Edition. For installation and usage instructions of the Docker images for this book, please refer to Chapter 1, Introduction to PostgreSQL.

Introduction to users and groups

PostgreSQL distinguishes between users and groups of users: the former represents someone, either a person or an application, that could connect to the cluster and perform activities; the latter represents a collection of users that share some common properties, most commonly permissions on cluster objects.

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 an (encrypted) password and are known to the PostgreSQL cluster. Similarly to operating system users, database users can be grouped into user groups in order to make their management easier.

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

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.

PostgreSQL ships with operating system tools to manage roles: createuser and dropuser. Both these commands open a connection to the cluster and perform the SQL commands mentioned above; therefore, the usage of these tools will not be explained in this chapter.

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, since 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.

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

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 known 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...

Summary

PostgreSQL allows you to define single users and groups of users, both represented by the SQL concept of roles. When a database connection attempt is made, PostgreSQL processes the connection information through the host-based access control so that it can immediately establish or reject the connection, depending on firewall-like rules. If the connection can be established, the credentials for the role are checked, and at last, the user is granted access.

Users and groups can be fine-tuned in terms of their granted permissions and connection limitations so that you can decide how many resources a single role can consume.

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...

Verify your knowledge

• What is the aim of the pg_hba.conf file?

  The pg_hba.conf file configures Host-Based-Access (HBA), a set of rules that define how a specific role (either a user or a group) can establish a connection to a specific database from a specific host or source, via a defined protocol. See the Managing incoming connections at the role level section for more details.

• How can you inspect the currently loaded HBA rules?

  The special catalog pg_hba_file_rules provides details about loaded rules. See the Inspecting pg_hba.conf rules section for more details.

• Does the order of rules within pg_hba.conf matter?

  Yes, the rules are evaluated from top to bottom, and the first matching rule causes the end of the evaluation. See the Order of rules in pg_hba.conf section for more details.

• Where can you find information about roles?

  The special catalogs pg_roles and pg_authid provide information about roles....

References

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

Learn more on Discord

To join the Discord community for this book – where you can share feedback, ask questions to the author, and learn about new releases – follow the QR code below:

https://discord.gg/jYWCjF6Tku