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:
cluster holds many
databases, which hold many
schemas that contain
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,
pb_ident.conf and reads them. It also imports the contents of
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
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
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]
pg_hba.conf configuration could look like this:
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
psql client is supposed to pass a certain username to the database. If you don't do this,
command is equivalent to
psql -U $USER database. Attempt to login as
a root will cause an error.
Written by Boris Burkov who lives in Moscow, Russia, loves to take part in development of cutting-edge technologies, reflects on how the world works and admires the giants of the past. You can follow me in Telegram