Schema-level access control in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this schema access query?

Consider a PostgreSQL database with a schema named sales. The user alice has been granted USAGE on the sales schema but no other privileges. What will be the result of the following query when run by alice?

SELECT * FROM sales.orders;

PostgreSQL

GRANT USAGE ON SCHEMA sales TO alice;
-- alice runs:
SELECT * FROM sales.orders;

A ERROR: permission denied for relation orders

B Returns all rows from sales.orders table

C ERROR: schema sales does not exist

D Returns no rows (empty result set)

💡 Hint

USAGE on a schema allows access to the schema itself but not to the tables inside it.

Granting USAGE on a schema allows the user to access objects in the schema if they have privileges on those objects. Since alice has no SELECT privilege on sales.orders, the query fails with a permission denied error.

🧠 Conceptual

intermediate

Which privilege is required to create tables in a schema?

In PostgreSQL, to create new tables inside an existing schema, which privilege must a user have on that schema?

A USAGE

B INSERT

C SELECT

D CREATE

💡 Hint

Think about what permission allows adding new objects inside a schema.

The CREATE privilege on a schema allows a user to create new objects such as tables within that schema. USAGE alone does not allow creation.

📝 Syntax

advanced

Identify the correct syntax to grant SELECT on all tables in a schema

Which of the following commands correctly grants SELECT privilege on all current tables in the inventory schema to user bob?

A GRANT SELECT ON SCHEMA inventory TO bob;

B GRANT SELECT ON ALL TABLES IN SCHEMA inventory TO bob;

C GRANT SELECT ON inventory.* TO bob;

D GRANT SELECT ON ALL TABLES inventory TO bob;

💡 Hint

Look for the exact phrase used in PostgreSQL to grant privileges on all tables in a schema.

The correct syntax is GRANT SELECT ON ALL TABLES IN SCHEMA inventory TO bob;. Other options are either invalid or grant privileges on the schema itself, not the tables.

❓ optimization

advanced

Best way to allow read access to all future tables in a schema

You want user carol to have SELECT access on all current and future tables in the reports schema. Which approach achieves this with minimal ongoing maintenance?

A Grant USAGE on the schema and ALTER DEFAULT PRIVILEGES to grant SELECT on future tables to carol.

B Grant SELECT on all current tables and repeat the grant manually for new tables.

C Grant CREATE on the schema to carol so she can create tables and access them.

D Grant SELECT on the schema itself.

💡 Hint

Think about how to automate privileges for tables created later.

Granting USAGE on the schema plus setting ALTER DEFAULT PRIVILEGES to grant SELECT on future tables ensures carol automatically gets access to new tables without manual grants.

🔧 Debug

expert

Why does this schema-level grant not allow table access?

User dave runs the following commands:

GRANT USAGE ON SCHEMA analytics TO dave;
SELECT * FROM analytics.events;

But he gets an error: permission denied for relation events. Why?

A Dave needs CONNECT privilege on the database first.

B The schema <code>analytics</code> does not exist.

C USAGE on schema does not grant any privileges on tables inside it.

D The table <code>events</code> is empty, so no rows are returned.

💡 Hint

Consider what USAGE on a schema actually allows.

USAGE on a schema allows access to the schema namespace but does not grant any rights on the tables inside. Dave needs explicit SELECT privilege on analytics.events to query it.