FrançaisGet started

Database accounts: who gets in, and how

Administering access to a PostgreSQL instance: login and group roles, inheritance of privileges, pg_hba.conf rules read top to bottom, expiry, connection limits and least privilege.

  • 24 cards
  • 5 topics
  • Free
Study this deck

The outline, topic by topic

Each topic is weighted the way the exam weighs it.

  1. Concepts
  2. Questions
  3. Sort and link
  4. Scenarios
  5. Understand

A few cards from the deck

  • Login role: what is it?

    Show the answer

    A role with the LOGIN attribute: it stands for an account that a person or an application uses to open a session.

    In PostgreSQL, user and group are a single notion, the role. CREATE USER is a CREATE ROLE … LOGIN.

  • The user app connects to the ventes database over TCP from 10.0.0.12. Which rule of this pg_hba.conf applies?

    ```text
    # TYPE DATABASE USER ADDRESS METHOD
    local all postgres peer
    host all all 127.0.0.1/32 scram-sha-256
    host ventes app 10.0.0.0/24 scram-sha-256
    host all all 10.0.0.0/16 reject
    host ventes app 10.0.0.12/32 trust
    host all all 0.0.0.0/0 scram-sha-256
    `

    • The third: scram-sha-256 password
    • The fifth, trust, because it is the most specific rule for this address
    • The fourth, reject, because it covers every address of the application's network
    • The sixth, scram-sha-256, because the server keeps the rule that covers the widest range
    Show the answer

    The third: scram-sha-256 password

    PostgreSQL reads the file top to bottom and applies the first rule that matches the connection: here the third. The following rules, even more specific (trust) or wider (reject), are never read.

  • … checks who is connecting; … checks what they may do once connected.

    Show the answer

    Authentication checks who is connecting; authorisation checks what they may do once connected.

    pg_hba.conf belongs to the first (who gets in, and from where), GRANT and REVOKE to the second (which objects they may touch).

  • Group role: what is it?

    Show the answer

    A role without LOGIN that holds privileges on objects and is granted to other roles, so that privileges are managed by job rather than by person.

    SELECT is granted once to lecture, then members are added to lecture. When someone changes job, a single GRANT or REVOKE is enough.

  • An application receives FATAL: no pg_hba.conf entry for host "10.0.0.12", user "app", database "ventes". What should be done?

    • Add a narrow rule for app, ventes and 10.0.0.12/32, then reload the configuration
    • Add host all all 0.0.0.0/0 trust at the top so that no connection is refused any more
    • Make app a superuser, since a superuser is not subject to pg_hba.conf
    • Create the app role with CREATE ROLE, since the database does not recognise this user name
    Show the answer

    Add a narrow rule for app, ventes and 10.0.0.12/32, then reload the configuration

    The message says no rule covers this address, account and database. Add the narrowest rule, then reload (SELECT pg_reload_conf();). Opening every address without a password clears the error by creating a hole. A superuser also goes through pg_hba.conf.

Study this deck