Access Control and Security

Picture this nightmare: you're the database admin, and suddenly your important customer data table just... vanishes. Who did it? Why? Maybe it was a rookie intern who accidentally typed DROP TABLE. Or maybe it was a malicious user who had access to the database... Whatever it was, it's too late to panic! All you can do is wake up, pull yourself together, and start learning about database security. Because a database without proper security is like a house with no foundation.

For databases, security has to be top-notch to prevent threats like:

• Unauthorized access: someone gets access to data they shouldn't see.
• Data leaks: passwords, credit cards, or confidential info end up in the wrong hands.
• SQL injections: a sneaky trick that lets an attacker mess with your database through poorly protected queries.
• Human errors: accidental data deletion or changes you can't undo.

In PostgreSQL, security is handled on several levels: from creating roles and managing privileges to restricting network access. This lets you fine-tune who can do what with your data.

Main Security Levels in PostgreSQL

In PostgreSQL, there are three main levels where you can manage access:

1. Database-level access control. At this level, you decide who can connect to your database and what a user can do once they're in. For example, you can block some users from logging in at all. Others — only allow them to read data. The key tool here is roles.

2. Table, row, and column-level access control.

In PostgreSQL, you can restrict access to data very precisely. You can:

• Let a user read only certain columns.
• Allow reading data in a table only if the user owns the row. This is called ROW LEVEL SECURITY (RLS), and we'll dig into it in later lectures.
• Restrict access only to certain parts of the database (schemas) where important data lives.

3. Network access configuration. The database can be set up to accept requests only from trusted clients. This is configured in the PostgreSQL config file called pg_hba.conf. With this file, you can, for example, allow connections only from the local machine or specific IP addresses.

PostgreSQL Security Tools

Let's break down what security tools PostgreSQL offers and how they work in real life.

Let's start with roles and privileges — this is the foundation of access control. In PostgreSQL, a role isn't just a user, it's something more flexible. It can represent a single person or a whole group. For example, you can create a role called manager that has full access to the orders table, and give the intern the intern role, which only has read rights — so they don't accidentally break anything.

You can set up roles pretty flexibly: give some people more rights, others less, and even let one role inherit the rights of another. Through roles, you decide who can connect to the database, which schemas and tables are available, and even which rows can be seen or changed.

Next up are configuration files. PostgreSQL has two key config files that play a big part in security.

The first is pg_hba.conf. It controls network access to the database. This is where you set up who can connect to the server, from which IP address, and what authentication method will be used. If you need to restrict access to certain machines or users — this is where you do it.

The second file is postgresql.conf. It manages general server settings, and among other things, it sets up logging and audit parameters. This lets you keep track of who does what, spot suspicious activity in time, and dig into the details if needed.

Finally, logging and audit. "Logs are your best friend." Sounds weird, but for a database admin, it's rule number one. In PostgreSQL, you can set up logging of all queries and user actions. This helps you figure out who did what to the database if something goes wrong.

Example: Protecting Data from SQL Injections

SQL injections are one of the most popular ways to attack a database. And it's super important to know how to defend against them. Imagine you have an app where a user enters their account ID to view their profile. And the app runs a query like this:

SELECT * FROM users WHERE id = 123;

But what if the user enters 123 OR 1=1 instead of a number? Then the query turns into something like:

SELECT * FROM users WHERE id = 123 OR 1=1;

And instead of a single record, the whole users table becomes available.

How do you protect yourself? PostgreSQL lets you use parameterized queries or prepared statements (PREPARE and EXECUTE), so user data never gets mixed with SQL code. Here's what it looks like:

PREPARE get_user_by_id (int) AS
SELECT * FROM users WHERE id = $1;

EXECUTE get_user_by_id(123);

More Real-World Threat Examples

Just so you really get why security matters, here are two real-life scenarios:

Example 1: "Employee deleted the whole table"

Once, at some company (actually, this happened more than once, and to lots of companies...) an intern accidentally typed this in the console:

DROP TABLE employees;

And 10 years of employee data went down the drain. How do you avoid this?

• Manage privileges! For example, the intern role should only have read access.
• Set up audit! Logs will show who ran the fatal query.

Example 2: "Data leak through an unencrypted connection"

If a user connects to the PostgreSQL server without encryption, their login and password can be intercepted by an attacker. Set up SSL and make sure connections are secure.

Key Tasks for Admins

To wrap up this intro, here are the three main tasks for any PostgreSQL admin:

• Access separation. Make sure only authorized users can do certain things.
• Encryption. Confidential data should always be stored and transmitted in encrypted form.
• Monitoring. Set up audit and keep an eye on suspicious activity in the logs.

In the next lectures, you'll learn how to create roles, manage access with GRANT and REVOKE, implement row-level access control, and use encryption to protect your data.