BorisBurkov.net

Postgres roles

October 09, 2018 4 min read

Postgres authentication and permission system sometimes feels like a total mess to me. This is a recap of how it works.

Here are the basic concepts of Postgres:

A cluster holds many databases, which hold many schemas that contain many tables.

Schemas (even with the same name) in different DBs are unrelated. Within the same DB they are something like namespaces for tables. Granting privileges for a schema only applies to this particular schema in the current DB (the current DB at the time of granting).

Every database starts with a schema public by default. That's a convention, and many settings start with it. Other than that, the schema public is just a schema like any other. More on schemas

Access to Postgres entities is regulated by the concept of a role. Role is a hybrid of user and group. In previous versions, postgres used to have users and groups, but now they are replaced with a single entity. So now 'user' role can be granted permissions to a 'group' role.

Potentially available permissions are listed here.

Postgres configuration typically consists of 3 files and a conf.d folder: the main file, postgresql.conf, specifies locations of two other files, pg_hba.conf and pb_ident.conf and reads them. It also imports the contents of conf.d directory.

Role authentication supports a number of mechanisms, which can be configured in pg_hba.conf. "hba" part stands for "Host-based authentication", this is the main configuration file, responsible for authentication settings.

Typically, a trusted authentication mechanism is enabled that allows users on the local machine to login to postgres without a password. E.g. within a docker container the database is accessible with psql, but not outside.

Mapping between the unix system users and postgres users is governed by pg_ident.conf configuration file. Records in it are of the form:

    # MAPNAME  SYSTEM-USERNAME  PG-USERNAME
# root is allowed to login as postgres
root_as_postgres postgres postgres
User123 LinuxUser PGUser

MAPNAME is the (otherwise freely chosen) map name that was used in pg_hba.conf. SYSTEM-USERNAME is the detected user name of the client. PG-USERNAME is the requested PostgreSQL user name. The existence of a record specifies that SYSTEM-USERNAME may connect as PG-USERNAME.

If SYSTEM-USERNAME starts with a slash (/), it will be treated as a regular expression. Optionally this can contain a capture (a parenthesized subexpression). The substring matching the capture will be substituted for \1 (backslash-one) if present in PG-USERNAME.

Multiple maps may be specified in pg_ident.conf and used by pg_hba.conf. See more examples on pg_ident.conf.

Maps, specified in pg_ident.conf, are then used in pg_hba.conf to determine, who can access the database over what kind of connection. Records in pg_hba.conf are of the form:

    # This file controls: which hosts are allowed to connect, how clients
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access. Records take one of these forms:
#
# local DATABASE USER METHOD [OPTIONS]
# host DATABASE USER ADDRESS METHOD [OPTIONS]
# hostssl DATABASE USER ADDRESS METHOD [OPTIONS]
# hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]

For example, pg_hba.conf configuration could look like this:

    # Default:
local all postgres trust

# "local" is for Unix domain socket connections only
local all all trust

# IPv4 local connections:
host all all 127.0.0.1/32 trust

# IPv6 local connections:
host all all ::1/128 trust

# Local root Unix user, passwordless access
local all postgres peer map=root_as_postgres

# Password hosts
host all all 0.0.0.0/0 password

Still, psql client is supposed to pass a certain username to the database. If you don't do this, psql database command is equivalent to psql -U $USER database. Attempt to login as a root will cause an error.

Links:


Boris Burkov

Written by Boris Burkov who lives in Moscow, Russia and Cambridge, UK, loves to take part in building future technologies, think about the world, we're living in at present and admires the giants of the past. You can follow me on Telegram