Personal Catalogue: Django & PostgreSQL — Chapter 9, Exercise 2
====================================================

TASK
Create the catalogue_app PostgreSQL role with exactly the privileges
shown in this chapter, then confirm — by trying and expecting it to
fail — that a query like DROP TABLE catalogue_item; run as that role
is correctly rejected with a real permissions error.

SOLUTION

1. Connect as the postgres superuser and create the role:

    psql -U postgres

    CREATE ROLE catalogue_app WITH LOGIN PASSWORD 'a-real-strong-password-here';
    GRANT CONNECT ON DATABASE catalogue TO catalogue_app;
    GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO catalogue_app;

2. Confirm the granted privileges work as expected by connecting as
   catalogue_app and running a real SELECT:

    psql -U catalogue_app -d catalogue -h localhost
    SELECT * FROM catalogue_item LIMIT 1;

   This should succeed and return real data (or an empty result if the
   table is empty, but no permissions error).

3. TRYING THE REJECTED OPERATION: while still connected as
   catalogue_app, run:

    DROP TABLE catalogue_item;

EXPECTED RESULT

PostgreSQL should reject this with a real error along the lines of:

    ERROR:  must be owner of table catalogue_item

This happens because DROP TABLE requires ownership (or a specific
DROP-level privilege) over the object being dropped, and the GRANT
statements above only granted SELECT/INSERT/UPDATE/DELETE - none of
which include ownership or schema-altering rights. The catalogue_app
role genuinely cannot destroy the table structure, exactly as the
chapter's own finding-box describes.

WHY THIS WORKS AS AN ANSWER
----------------------------
It creates the role with exactly the documented privileges, confirms
the intended operations do work first, and then specifically attempts
the operation that's supposed to fail, reporting the actual real error
message PostgreSQL returns rather than just asserting the restriction
exists.