Why database security matters in PostgreSQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why is database security important?

Imagine you have a diary where you write your secrets. Why should you keep it locked and safe?

Choose the best reason why database security is important.

A To prevent unauthorized people from reading or changing the data.

B To make the database run faster.

C To allow everyone to access the data easily.

D To reduce the size of the database files.

💡 Hint

Think about what happens if someone reads your diary without permission.

Database security protects sensitive information from unauthorized access or changes, just like locking your diary keeps your secrets safe.

❓ query_result

intermediate

Identify the effect of a missing user permission

Given a PostgreSQL database with a table employees, a user tries to run this query:

SELECT * FROM employees;

The user does not have SELECT permission on the table. What error message will PostgreSQL return?

PostgreSQL

SELECT * FROM employees;

A ERROR: syntax error at or near "*"

B ERROR: permission denied for table employees

C ERROR: column "*" does not exist

D ERROR: relation "employees" does not exist

💡 Hint

Think about what happens when a user tries to access a table without permission.

PostgreSQL returns a permission denied error when a user lacks SELECT rights on a table.

📝 Syntax

advanced

Find the correct SQL to grant read access

You want to allow a user named report_user to read data from the sales table only. Which SQL command correctly grants this permission?

A GRANT SELECT * FROM sales TO report_user;

B GRANT READ ON sales TO report_user;

C GRANT SELECT ON TABLE sales TO report_user;

D ALLOW SELECT ON sales FOR report_user;

💡 Hint

PostgreSQL uses the GRANT statement with SELECT privilege for read access.

The correct syntax to grant read access is GRANT SELECT ON TABLE sales TO report_user;. Other options are invalid SQL or incorrect syntax.

❓ optimization

advanced

Best practice to secure sensitive columns

You have a table customers with sensitive columns like credit_card_number. What is the best way to protect this data in PostgreSQL?

A Allow all users to access the table but log all queries.

B Store the data in plain text but hide the table from users.

C Delete the sensitive columns to avoid risk.

D Encrypt the sensitive columns and restrict access using roles.

💡 Hint

Think about protecting data both by encryption and access control.

Encrypting sensitive data and controlling access with roles is a strong security practice. Other options either expose data or remove it unnecessarily.

🔧 Debug

expert

Identify the security risk in this PostgreSQL setup

A database admin created a user readonly and granted SELECT on all tables. However, the user can still update data. What is the most likely cause?

Consider this setup:

CREATE USER readonly WITH PASSWORD 'pass';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

A The user has additional roles or permissions granting UPDATE rights.

B The GRANT SELECT command also allows UPDATE implicitly.

C The password is too weak, allowing unauthorized access.

D PostgreSQL does not support restricting UPDATE permissions.

💡 Hint

Check if the user belongs to other roles or has extra permissions.

Granting SELECT alone does not allow UPDATE. If the user can update, they likely have other roles or permissions granting that right.

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