>_ IzzyLab

Hands-on task

This task is step 17 of the course “PostgreSQL: from your first database to transactions”

PostgreSQL: read-only user

hard~25 min

PostgreSQL is already installed, but the server isn't running. Start it and configure: a database appdb, a table items inside it (any columns, at least one row) and a user reader with password readpass who can SELECT from items but cannot INSERT. The server must accept TCP connections on 127.0.0.1 — and right now TCP is turned off in its config.

The server config is /etc/postgresql/16/main/postgresql.conf — open it in the Editor tab.

What you'll practise:
  • postgresql
  • roles and grant
  • least privilege
  • pg_hba and tcp
  • psql

Topic breakdown

This task is about the principle of least privilege: a role is granted exactly the right to read a table and nothing more. Access in PostgreSQL is layered, and a single GRANT SELECT on a table is not enough — for the role to even reach the data you also need CONNECT on the database and USAGE on the schema; the write privilege is simply never granted. A separate layer is the network: clients connect over TCP, so it matters that the server listens on the right address (listen_addresses) and that in pg_hba.conf a method like scram-sha-256 or md5 is allowed for password login.

What's checked

  1. database appdb created
  2. table items contains rows
  3. reader can SELECT from items over TCP
  4. reader cannot INSERT

What it looks like