>_ IzzyLab

Hands-on task

This task is step 7 of the course “Databases in an evening: SQLite, PostgreSQL, Redis”

A reader for the library database

medium~7 min

A storefront application will connect to the library database. Create a role reader for it that can log in (LOGIN) with the password pages42, and grant it the right to connect to the library database (CONNECT) — everyone else has had that right revoked in this database.

What you'll practise:
  • postgresql
  • create role
  • login
  • password
  • grant connect

Topic breakdown

In PostgreSQL users and groups are one entity, the role. A role with the LOGIN attribute can connect; without it, it serves as a group for handing out privileges. The password is a role attribute checked on network login (-h 127.0.0.1), but on its own it opens nothing: access to each database is a separate CONNECT privilege, which by default belongs to the pseudo-role PUBLIC, and careful admins revoke it and grant it explicitly with GRANT. So logging in takes three independent things: the role, the right to log in with a password, and the right to the specific database.

What's checked

  1. role reader can log in
  2. reader has CONNECT on library
  3. reader logs in to library with password pages42

What it looks like