Postgres in depth · Chapter 56 of 65
How extensions work
By the end of this chapter you can find, install and inspect a Postgres extension, store passwords safely with pgcrypto, and make stable ids with uuid-ossp. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Extensions and graduation
- 5 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.
Why you need this
Sana found the old admin panel's logins: three staff accounts, with the passwords saved as plain text. Anyone who could read that table could log in as anyone. Kabir wants something else: every certificate gets a public link, and the link must not show LCO's internal ids, yet the same certificate must always get the same link.
Postgres itself has no function for safe password hashing, and its own uuid function only makes random ones. Both live in extensions: packages that add functions, types, operators and index methods to a database. This class uses ten of them. This chapter shows how they work, with the first two.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
install.sql: install pgcrypto and uuid-ossp
Install the extensions pgcrypto and uuid-ossp in this database, so that running the file twice still works. Then list every extension installed in this database, one row each.
Return three columns: name (the extension's name), version (its installed version) and schema (the name of the schema its objects are in). Order by name.
- Task 2
admins.sql: store the admin passwords safely
Create the table admin_users with two columns: email (text, the primary key) and password_hash (text, never empty). Insert the three staff accounts, storing a bcrypt hash of each password, never the password itself:
sana@lco.dev with the password masala-chai-42, arjun@lco.dev with masala-chai-42, and priya@lco.dev with filter-coffee-7.
The file installs pgcrypto itself if it is missing: the Check runs it in a fresh copy of LCO's database.
- Task 3
login.sql: check a typed password
Someone typed the password masala-chai-42 on the admin login page. For every row of admin_users (the table from task 2; the Check's copy has it with its own hashes), say whether that password is the user's password.
Return two columns: email and matches (true or false). Order by email.
Install pgcrypto in the file if it is missing, as in task 2.
- Task 4
certificate-ids.sql: stable public ids for certificates
Kabir wants a public id for every certificate of the course Postgres for Developers: a version 5 uuid made from the URL namespace and the text https://lco.dev/certificates/ followed by the certificate's code (for example https://lco.dev/certificates/LCO-ABC123).
Return three columns: code, student (the student's name) and public_id. Order by code.
Install uuid-ossp in the file if it is missing.
- Task 5 · interview question
Interview: extension-objects.sql, what each extension brought
An interviewer asks: after you install pgcrypto and uuid-ossp, how many functions does each installed extension own? Answer with a query over the catalogs. Install both extensions in the file first.
Return two columns: extension (its name) and functions (how many functions belong to it). Order by extension.
Careful: counting the functions in a schema is not the same thing. public holds the functions of several extensions, and functions that belong to none.
What you will learn
- Extensions: what one is, where it lives, what CREATE EXTENSION does.
- pg_extension and \dx: what is installed, and what each one brought.
- pgcrypto: hashing, and passwords with crypt and gen_salt.
- uuid-ossp: random uuids, and name-based ones that never change.
More in Extensions and graduation
Build "How extensions work" yourself
LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.