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.