Users, privileges and backups

In a real database the data is valuable: personal details, money, grades. Protecting it from unauthorised access and from loss is an essential part of running a database.

Users and roles

Every application and every person connects with their own account and can do only what they need.

In PostgreSQL everything is a role: users and groups alike.

CREATE ROLE report_reader LOGIN PASSWORD 'kuchli-parol';
GRANT CONNECT ON DATABASE university TO report_reader;
GRANT SELECT ON students, grades TO report_reader;

Privileges

To take a privilege away: REVOKE INSERT ON students FROM report_reader;

The principle of least privilege: reports get only SELECT; a website can write to its own tables but cannot DROP; the administrator account is for administrators only. The shared databases on SQLab have exactly this read-only access — which is why DELETE fails there.

SQL injection

The best-known attack. If an application pastes the user’s text straight into a query:

-- the application builds this query:
SELECT * FROM users WHERE email = '<what the user typed>';

-- the attacker types this instead of an e-mail:  ' OR '1'='1
SELECT * FROM users WHERE email = '' OR '1'='1';   -- every user!

The defence is parameterised queries: the value is sent separately from the query and is never read as SQL.

db.query('SELECT * FROM users WHERE email = $1', [email])   -- PostgreSQL
db.query('SELECT * FROM users WHERE email = ?', [email])    -- MySQL

The rule is simple: never glue user text into a query as a string.

Passwords

A database never stores passwords in plain form — only their hash (bcrypt, scrypt, argon2). Even if the database is stolen, the passwords cannot be read.

Backups

A failed disk, a careless DELETE, malware — without a backup each of these is a disaster.

pg_dump -U postgres university > university.sql     # make a backup
psql -U postgres -d university_new < university.sql  # restore it

Good practice: automatic daily backups, stored on another server, and regularly testing the restore — a backup you have never restored is not a backup.

Remember

• Every application gets its own account with only the privileges it needs (GRANT / REVOKE).
• Parameterised queries protect against SQL injection.
• Passwords are stored only as hashes; backups are regular and tested.