Postgres roles

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:

• Entities
• Nested roles
• Schemas
• Authentication mechanisms
• Permissions
• psql and system user
• Comments in Postgres config files, created by this Ansible role, are very helpful