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.