Why database security matters in PostgreSQL - See It in Action

Why Database Security Matters

📖 Scenario: You are working as a database administrator for a small company. The company stores customer information in a PostgreSQL database. You want to understand why database security is important and how to protect sensitive data.

🎯 Goal: Build a simple PostgreSQL setup that includes a table with sensitive data, a role with limited permissions, and a query that demonstrates controlled access to the data.

📋 What You'll Learn

Create a table called customers with columns id, name, and email

Insert three specific customer records into the customers table

Create a role called readonly_user with SELECT permission only on the customers table

Write a query to select all customer names and emails using the readonly_user role

💡 Why This Matters

🌍 Real World

Companies must protect sensitive customer data from unauthorized access to comply with laws and maintain trust.

💼 Career

Database administrators and developers use roles and permissions to secure databases and control who can see or change data.

1

Create the customers table

Write a SQL statement to create a table called customers with three columns: id as an integer primary key, name as text, and email as text.

PostgreSQL

-- Create the customers table with id, name, and email columns
-- Your code here

Hint

Use CREATE TABLE with column definitions. Remember to set id as the primary key.

2

Insert customer records

Insert these three customers into the customers table: (1, 'Alice Johnson', '[e-mail]'), (2, 'Bob Smith', '[e-mail]'), and (3, 'Carol White', '[e-mail]').

PostgreSQL

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
);
-- Insert three customer records below
-- Your code here

Hint

Use a single INSERT INTO statement with multiple rows.

3

Create a read-only role

Create a role called readonly_user and grant it SELECT permission on the customers table only.

PostgreSQL

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
);

INSERT INTO customers (id, name, email) VALUES
(1, 'Alice Johnson', '[e-mail]'),
(2, 'Bob Smith', '[e-mail]'),
(3, 'Carol White', '[e-mail]');

-- Create readonly_user role and grant SELECT permission
-- Your code here

Hint

Use CREATE ROLE to make the role, then GRANT SELECT ON customers TO readonly_user to give read-only access.

4

Query customer data as readonly_user

Write a SQL query to select the name and email columns from the customers table. Assume you are connected as readonly_user to demonstrate controlled access.

PostgreSQL

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
);

INSERT INTO customers (id, name, email) VALUES
(1, 'Alice Johnson', '[e-mail]'),
(2, 'Bob Smith', '[e-mail]'),
(3, 'Carol White', '[e-mail]');

CREATE ROLE readonly_user LOGIN;
GRANT SELECT ON customers TO readonly_user;

-- Select name and email from customers as readonly_user
-- Your code here

Hint

Use a simple SELECT statement to get the name and email columns.

Practice

1. Why is database security important in PostgreSQL?

easy

A. To allow everyone to edit data freely

B. To make queries run faster

C. To increase the size of the database

D. To protect data from unauthorized access

Solution

1. Step 1: Understand the purpose of database security

   Database security is designed to keep data safe and prevent unauthorized users from accessing or changing it.

2. Step 2: Identify the correct reason among options

   The option "To protect data from unauthorized access" correctly identifies the purpose of database security.

3. Final Answer:

   To protect data from unauthorized access -> Option D

4. Quick Check:

   Database security = Protect data [OK]

Hint: Security means protecting data from unauthorized users [OK]

Common Mistakes:

• Confusing security with performance
• Thinking security increases database size
• Believing security allows open editing

2. Which PostgreSQL command is used to give a user permission to SELECT data from a table?

easy

A. ALLOW SELECT ON table_name TO user_name;

B. GRANT SELECT ON table_name TO user_name;

C. PERMIT SELECT FROM table_name TO user_name;

D. ACCESS SELECT ON table_name TO user_name;

Solution

1. Step 1: Recall PostgreSQL syntax for permissions

   PostgreSQL uses the GRANT command to give permissions to users.

2. Step 2: Match the correct syntax

   The correct syntax is "GRANT SELECT ON table_name TO user_name;", which is the only valid command among the options.

3. Final Answer:

   GRANT SELECT ON table_name TO user_name; -> Option B

4. Quick Check:

   GRANT = give permission [OK]

Hint: GRANT is the keyword to give permissions in PostgreSQL [OK]

Common Mistakes:

• Using ALLOW instead of GRANT
• Using PERMIT or ACCESS which are invalid
• Incorrect command order

3. Given the commands:
CREATE TABLE employees(id SERIAL PRIMARY KEY, name TEXT);
GRANT SELECT ON employees TO guest_user;
What will happen if guest_user tries to run INSERT INTO employees(name) VALUES('Alice');?

medium

A. The insert will succeed and add a new row

B. The insert will succeed but data will not be saved

C. The insert will fail with a permission error

D. The insert will cause a syntax error

Solution

1. Step 1: Analyze granted permissions

   The user guest_user has only SELECT permission on the employees table, which allows reading data but not modifying it.

2. Step 2: Understand the effect of INSERT without permission

   Trying to INSERT without INSERT permission causes a permission error in PostgreSQL.

3. Final Answer:

   The insert will fail with a permission error -> Option C

4. Quick Check:

   INSERT without permission = error [OK]

Hint: SELECT permission does not allow INSERT operations [OK]

Common Mistakes:

• Assuming SELECT allows INSERT
• Thinking data won't save silently
• Confusing permission error with syntax error

4. You wrote this command to restrict user access:
REVOKE SELECT ON employees FROM guest_user;
But guest_user still can SELECT data. What is the likely problem?

medium

A. guest_user has SELECT permission through a role or group

B. REVOKE command syntax is incorrect

C. guest_user is the database owner

D. SELECT permission cannot be revoked

Solution

1. Step 1: Understand REVOKE command effect

   REVOKE removes direct permissions from a user but does not affect permissions inherited from roles or groups.

2. Step 2: Identify why guest_user still has access

   If guest_user belongs to a role or group with SELECT permission, they keep access despite the REVOKE.

3. Final Answer:

   guest_user has SELECT permission through a role or group -> Option A

4. Quick Check:

   Inherited permissions override REVOKE [OK]

Hint: Check roles/groups for inherited permissions [OK]

Common Mistakes:

• Assuming REVOKE always removes access
• Thinking syntax is wrong without checking
• Believing owners cannot lose permissions

5. A company wants to ensure only HR staff can view employee salaries in PostgreSQL. Which approach best secures this sensitive data?

hard

A. Store salaries in a separate table and grant SELECT only to HR role

B. Create a view showing only non-sensitive columns and grant SELECT on it to all users

C. Grant SELECT on the salary column to all users but restrict UPDATE

D. Grant SELECT on the entire employees table to all users

Solution

1. Step 1: Identify the need to protect sensitive salary data

   Salaries are sensitive and should be accessible only to HR staff, not all users.

2. Step 2: Evaluate options for restricting access

   Storing salaries in a separate table and granting SELECT only to the HR role isolates the sensitive data and restricts access effectively.

3. Step 3: Why other options are less secure

   Grant SELECT on the entire employees table to all users exposes all data; B exposes non-sensitive data but not salaries; C grants salary SELECT to all users, which is unsafe.

4. Final Answer:

   Store salaries in a separate table and grant SELECT only to HR role -> Option A

5. Quick Check:

   Separate sensitive data + restrict access = secure [OK]

Hint: Separate sensitive data and restrict role access [OK]

Common Mistakes:

• Granting broad SELECT permissions
• Exposing sensitive columns in views
• Not using roles to control access