Databases, schemas, and users

Written Explanation

The layers

server (one installation)
  database "library"
    schema "public"
      tables: authors, books, members, loans
    schema "staging"
      tables: ...
  database "postgres"
  ...

According to the PostgreSQL manual, a schema is a named container inside one database that holds tables and other objects. Two different schemas can contain tables with the same name, and schemas cannot be nested. Every new database starts with a schema named public, and an unqualified table name goes there. So CREATE TABLE books (...) is the same as CREATE TABLE public.books (...).

A database is a bigger boundary than a schema. When you connect with psql -d library, you connect to that one database; to work in another one you reconnect (in psql, \c name). That makes a database a natural unit for one application: Library data in library, something else in its own database.

Qualified names and the search path

You can name a table with its schema: staging.tmp_books. If you write just books, PostgreSQL looks through a list of schemas called the search path. The default setting is "$user", public. The first entry means "a schema named after the current user, if one exists", and since it normally does not, public is where plain names are found. SELECT current_schema(); tells you where new tables would be created.

For this course, you will keep to the public schema and use a second schema only to see how it works. Two schemas are useful when one database holds several areas, such as staging data separate from live data.

One recent change to know about: from PostgreSQL 15 on, ordinary users no longer get the right to create objects in public by default. The manual says databases upgraded from older versions keep the old behavior. In our setup the user who owns the database can create tables, which is why Lesson 6 works. If you ever get permission denied for schema public as a non-owner, that is the cause.

Roles, users, and superusers

PostgreSQL uses one concept, the role, for both individual people and groups. A role that can log in is what we usually call a user. CREATE USER is the same as CREATE ROLE plus the LOGIN attribute. Roles belong to the whole server, not to one database, so a name is unique across the installation.

Two attributes matter here. By default a new role is not a superuser (NOSUPERUSER) and cannot create databases (NOCREATEDB). A superuser bypasses all permission checks, which is why the manual advises granting it only when necessary. The postgres role created at installation is a superuser. You can see the difference with \du:

                              List of roles
  Role name  |                         Attributes
-------------+------------------------------------------------------------
 library_app |
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS

library_app has no special attributes: it can log in (it was created with CREATE USER) and own what it creates, and nothing more.

Creating the Library user and database

Connect as postgres once, for setup only:

CREATE USER library_app WITH PASSWORD 'change-me';
CREATE DATABASE library OWNER library_app;

• The user is created first, because the database names it as its owner.
• OWNER library_app makes that user the owner of the new database. The manual says you can only name another role as owner if you may switch to that role, which a superuser can.
• 'change-me' is a placeholder. A password typed into SQL like this travels to the server in plain text and may be saved in your client history or in the server log, as the manual for CREATE ROLE warns. In real work, set the password with \password library_app in psql, which sends it encrypted. For a local learning server a throwaway value is acceptable, but never reuse a real one.

CREATE DATABASE cannot be run inside a transaction block (Lesson 5 explains what that is), so run it by itself.

Now connect as the new user:

psql -U library_app -d library

Check who you are: SELECT current_user, current_database(); returns library_app and library.

Why not postgres?

The superuser can read, change, or drop everything: every database, every user. If your application connects as postgres, then:

• a bug (or a deliberate attack such as SQL injection, which later modules cover) can drop any database on the server;
• a leaked configuration file hands over total control, not just access to Library data;
• you cannot tell from the user name which application did something.

Giving each application its own user, which can reach only its own database, is called least privilege: give only the rights the job needs. You can see it working. Logged in as library_app, try to create a user or a database:

library=> CREATE ROLE intruder LOGIN;
ERROR:  permission denied to create role
DETAIL:  Only roles with the CREATEROLE attribute may create roles.
library=> CREATE DATABASE other;
ERROR:  permission denied to create database

The prompt ends in => for an ordinary user and =# for a superuser. That small character is a handy reminder of how powerful your session is.

Note one limit on what this lesson claims: the database owner still has broad power inside its own database (it may create and drop tables there), which is exactly what the Lab needs. Finer-grained rights (granting only SELECT on some tables) come in later levels.