Skip to main content

6.9 Open the database your Git server is running on

You've been running a database since lesson 6.6 and nobody mentioned it.

Every application you will ever operate keeps its state somewhere, and that somewhere is almost always a database. Your Git server knows who you are, which repositories exist, and who is allowed into them, and it does not keep that in a text file. Today you open it and ask it questions.

The reason this belongs in an infrastructure course, rather than a developer one, is that "look in the database" is an operational move. Why does this user still show as active after I deleted them? Which accounts have admin? How many repositories are actually in use? Support articles answer those questions with screenshots of a web interface that changes every release. The database answers them directly and doesn't change.

You do not need to become a database administrator. You need to be able to open one, find your way around, and get an answer out.

Two kinds of database, in one paragraph each​

Relational databases store data in tables: rows and columns, with a strict shape agreed in advance. A user table has a column for the name, one for whether they're an admin, and every row has exactly those columns. Relationships between tables are the point, hence the name: a repository row doesn't repeat its owner's name, it stores the owner's id and you join the two together when you need both. You talk to them in SQL (Structured Query Language). PostgreSQL, MySQL, SQL Server, and SQLite are all this kind, and they are the overwhelming majority of what you'll meet.

Non-relational, often called NoSQL, relaxes that. Documents can have different fields from each other, and there's no schema agreed in advance. That flexibility suits data whose shape you don't know yet, which is why the search and logging tools in Module 12 use one. The trade is that the database can no longer enforce consistency for you, so the application has to.

Gitea uses SQLite, which is relational and unusual in one respect: there's no server process at all. The entire database is a single file on disk. That makes it a good first one to open, and it's the same SQL you'd type against a Postgres cluster with a thousand connections.

Find the file​

In lesson 6.6 you mapped ./data into the container. Everything Gitea owns lives there, including its database:

cd ~/gitea
ls -lh data/gitea/gitea.db

Expect a single file, a few megabytes at most.

That is worth pausing on. Your entire Git server, every repository's metadata, every account, is that one file plus the repositories beside it. It's also the clearest possible illustration of why lesson 6.6 told you that backing up ./data gets you everything.

Install the command-line client:

sudo apt install sqlite3

Open it without breaking it​

Open it read-only. The application is running right now and expects to be the only thing writing to this file:

sqlite3 "file:data/gitea/gitea.db?mode=ro" -header -column

The ?mode=ro is not decoration. A stray UPDATE or DELETE against a live application's database is one of the classic ways to ruin an afternoon, and unlike a bad config change there is no reload that undoes it. Read-only makes the mistake impossible rather than unlikely.

You'll get a sqlite> prompt. Two things before anything else: .tables lists what exists, and .quit gets you out. If you ever find yourself stuck at a ...> prompt, you forgot the semicolon that ends a statement.

Least privilege

Opening read-only is the principle from lesson 5.6 applied to data. You need to read this database, so take exactly that and nothing more.

In production the same idea is a separate database account with SELECT permission and no write permission, handed to reporting tools and analysts. Same instinct as sudo, as PermitRootLogin no in lesson 6.3, and as the offline root you'll build in Module 7: hold the smallest capability that does the job.

Ask it questions​

.tables

A long list. Gitea has dozens of tables and you only care about two today: user and repository.

.schema shows how a table is built, which is how you find out what columns exist without guessing:

.schema user

Now the query that everything else is a variation on. SELECT picks columns, FROM picks the table:

SELECT name, is_admin FROM user;
name      is_admin
--------  --------
sokoth    1
testuser  0

Your own account, and whether it holds admin. That 1 and 0 are how SQLite stores true and false.

WHERE filters rows:

SELECT name FROM user WHERE is_admin = 1;

That is the "who has admin on this system" question from lesson 5.6, asked of a different system. It's the same question every audit asks, and you can now answer it for anything that stores its users in a database.

COUNT answers "how many" without printing everything:

SELECT COUNT(*) FROM repository;
3

ORDER BY sorts, and LIMIT cuts the list short. Together they answer most "top N" questions you'll ever be asked:

SELECT name, num_stars FROM repository ORDER BY num_stars DESC LIMIT 5;

The join, which is the whole point of relational​

The repository table stores an owner_id, not an owner's name. Ask for repositories on their own and you get a number you can't read:

SELECT name, owner_id FROM repository;

JOIN stitches the two tables together on the value they share:

SELECT r.name AS repo, u.name AS owner
FROM repository r
JOIN user u ON r.owner_id = u.id
ORDER BY r.name;
repo         owner
-----------  --------
lab-journal  sokoth
sandbox      testuser
scripts      sokoth

Read the query aloud and it says what it does: take repositories, attach the user whose id matches the repository's owner_id, show me the two names. The r and u are just short nicknames so the rest is readable.

This is why the owner's name is stored once, in one place. Rename that user and every repository reports the new name immediately, because nothing copied it. Store the name in both tables and you have two versions of the truth and no way to know which is current. That single idea is what "relational" is really about, and it's most of database design.

The famous way this goes wrong​

You are taking a security course and you have just learned to write queries, so it would be strange not to mention the vulnerability those queries are famous for.

SQL injection happens when a program builds a query by gluing user input into a string. Imagine Gitea looked up a user like this:

SELECT * FROM user WHERE name = '<whatever they typed>';

Type sokoth and it asks a sensible question. Type this instead:

' OR '1'='1

and the query the database receives becomes:

SELECT * FROM user WHERE name = '' OR '1'='1';

'1'='1' is always true, so it matches every row. The input stopped being data and became part of the command.

That is the whole idea, and it is worth holding onto in general terms: somewhere, a boundary between data and instructions was not enforced. The same shape turns up in command injection, in cross-site scripting, and in places that have not been invented yet.

The fix is not to filter out quote marks, which is the tempting answer and a losing game. It is parameterised queries: the program sends the query and the values separately, so the database is told "this is the structure, and this is a value", and no value can ever change the structure.

# Wrong. The value becomes part of the command.
cur.execute("SELECT * FROM user WHERE name = '" + name + "'")

# Right. The value is passed separately and can never be code.
cur.execute("SELECT * FROM user WHERE name = ?", (name,))

You are not writing the application here, so you will not be fixing this in Gitea. What you now have is the ability to recognise the pattern in code you read, and to know what question to ask when someone says an application is "sanitising input".

This is why you opened the database read-only

The ?mode=ro earlier was least privilege applied to your own access. The same principle is the second line of defence against injection: an application whose database account can only SELECT cannot be made to DROP TABLE, however badly its queries are built.

Defence in depth means the second control matters precisely because the first one sometimes fails. You will meet this idea again in Module 14, from the other side.

What carries forward​

The SQL you just used is not SQLite-specific. SELECT, WHERE, COUNT, ORDER BY, and JOIN work essentially unchanged against PostgreSQL, MySQL, and SQL Server. What changes is how you connect and who manages the server, not how you ask.

You'll use this again in Module 13, where a vulnerability scanner hands you thousands of findings across dozens of hosts. "Which of my machines has a finding that CISA lists as actively exploited" is a join between two tables, and it's a far better tool than scrolling a report.

Make it yours​

  1. Find out when your account was created. The column is created_unix and it holds a Unix timestamp, which is seconds since 1970. SQLite can translate it: SELECT name, datetime(created_unix, 'unixepoch') FROM user;
  2. Count how many repositories are private versus public, in one query. You'll want COUNT(*), and GROUP BY is_private to split the count into groups rather than totalling everything.
  3. Harder, and genuinely useful: list every user who owns no repositories at all. There are a few ways; a LEFT JOIN where the repository side comes back empty is the classic one. If it fights you, this is a good thing to take to Claude using lesson 1.6's rule: ask it to explain the difference between JOIN and LEFT JOIN, then write the query yourself.