Why database security matters in PostgreSQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all columns from the users table.

PostgreSQL

SELECT [1] FROM users;

Drag options to blanks, or click blank then click option'

A*

BALL

Ccolumns

Deverything

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'ALL' instead of '*'

Writing 'columns' which is not valid SQL syntax

The asterisk (*) selects all columns from the table.

2fill in blank

medium

Complete the code to restrict access to only users with role 'admin'.

PostgreSQL

SELECT * FROM users WHERE role = [1];

Drag options to blanks, or click blank then click option'

A'guest'

B'user'

C'admin'

D'manager'

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'user' or 'guest' which have less privileges

Forgetting to put quotes around the role name

Only users with the role 'admin' should be selected for secure access.

3fill in blank

hard

Fix the error in the code to grant SELECT permission on the employees table to user 'john'.

PostgreSQL

GRANT SELECT ON [1] TO john;

Drag options to blanks, or click blank then click option'

Adatabase

Btable employees

Cuser john

Demployees

Attempts:

3 left

💡 Hint

Common Mistakes

Adding 'table' keyword which causes syntax error

Using 'database' instead of table name

Including 'user john' which is incorrect syntax

In PostgreSQL, you grant permissions on the table name directly, not with extra keywords.

4fill in blank

hard

Fill both blanks to create a user 'alice' with password 'secure123' and grant her SELECT permission on the sales table.

PostgreSQL

CREATE USER [1] WITH PASSWORD [2];
GRANT SELECT ON sales TO alice;

Drag options to blanks, or click blank then click option'

A'alice'

B'secure123'

Calice

Dsecure123

Attempts:

3 left

💡 Hint

Common Mistakes

Quoting the username which causes error

Not quoting the password string

Usernames are not quoted in CREATE USER, but passwords must be quoted strings.

5fill in blank

hard

Fill all three blanks to revoke INSERT and UPDATE permissions from user 'bob' on the customers table.

PostgreSQL

REVOKE [1], [2] ON customers FROM [3];

Drag options to blanks, or click blank then click option'

AINSERT

BUPDATE

Cbob

DDELETE

Attempts:

3 left

💡 Hint

Common Mistakes

Including DELETE which is not requested

Quoting the username 'bob'

Mixing up the order of permissions

We revoke INSERT and UPDATE privileges from user 'bob' to restrict data changes.

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