Schema-level access control in PostgreSQL - Deep Dive

Overview - Schema-level access control

What is it?

Schema-level access control is a way to manage who can see or change groups of database objects organized inside a schema. A schema is like a folder that holds tables, views, and other database items. By controlling access at the schema level, you can easily allow or restrict users from working with all objects inside that folder without setting permissions on each object individually. This helps keep the database secure and organized.

Why it matters

Without schema-level access control, managing permissions becomes tedious and error-prone because you would have to set permissions on every single table or object. This can lead to mistakes where unauthorized users get access or authorized users get blocked. Schema-level control simplifies security, reduces mistakes, and helps protect sensitive data in real-world applications like banking or healthcare systems.

Where it fits

Before learning schema-level access control, you should understand basic database concepts like tables, users, and permissions. After this, you can learn about row-level security and more advanced database security features. Schema-level control is a foundational step in securing a database.

Mental Model

Core Idea

Schema-level access control lets you manage permissions for a whole group of database objects at once by controlling access to their container, the schema.

Think of it like...

Imagine a filing cabinet with folders inside. Instead of locking each paper inside the folder, you lock the entire folder. Whoever has the key to the folder can access all papers inside without needing separate keys for each paper.

┌───────────────┐
│   Database    │
│  ┌─────────┐  │
│  │ Schema  │  │
│  │ ┌─────┐ │  │
│  │ │Table│ │  │
│  │ └─────┘ │  │
│  │ ┌─────┐ │  │
│  │ │View │ │  │
│  │ └─────┘ │  │
│  └─────────┘  │
└───────────────┘

Access control is applied at the Schema level, affecting all objects inside.

Build-Up - 7 Steps

1

Foundation

Understanding Database Schemas

Concept: Introduce what a schema is and its role in organizing database objects.

A schema is like a container or folder inside a database. It holds tables, views, functions, and other objects. Schemas help organize these objects logically and avoid name conflicts. For example, you might have a schema called 'sales' for sales data and another called 'hr' for human resources data.

Result

You understand that schemas group related database objects and act as namespaces.

Knowing schemas are containers helps you see why controlling access at this level can simplify permission management.

2

Foundation

Basics of Database Permissions

Concept: Explain how permissions control what users can do in a database.

Permissions (or privileges) tell the database who can read, write, or change data. Common permissions include SELECT (read), INSERT (add), UPDATE (change), and DELETE (remove). Permissions can be given to users or roles on specific objects like tables or schemas.

Result

You know that permissions protect data by limiting user actions.

Understanding permissions is essential before learning how to apply them at the schema level.

3

Intermediate

Granting Permissions on Schemas

🤔Before reading on: do you think granting permissions on a schema automatically gives access to all objects inside it? Commit to your answer.

Concept: Learn how to give users rights to use or create objects inside a schema.

In PostgreSQL, you can use the GRANT command to give permissions on a schema. For example, GRANT USAGE ON SCHEMA sales TO user1; lets user1 access objects in the sales schema. However, USAGE alone does not let them read tables; you must also grant SELECT on tables. You can also grant CREATE to allow making new objects inside the schema.

Result

You can control who can access or add objects inside a schema using GRANT commands.

Knowing that schema permissions control access to the container but not automatically to objects inside prevents common security mistakes.

4

Intermediate

Revoking Schema Permissions

🤔Before reading on: if you revoke USAGE on a schema, can a user still access tables inside? Commit to your answer.

Concept: Understand how to remove permissions and what effects revoking has.

REVOKE removes permissions previously granted. If you REVOKE USAGE ON SCHEMA sales FROM user1;, user1 cannot access any objects inside sales, even if they have table-level permissions. Revoking schema permissions is a quick way to block access to many objects at once.

Result

You can deny access to entire groups of objects by revoking schema permissions.

Understanding revoking at schema level helps you quickly secure or restrict users without changing many individual permissions.

5

Intermediate

Default Privileges and Schema Access

🤔Before reading on: do new tables inside a schema inherit permissions automatically? Commit to your answer.

Concept: Learn how default privileges affect new objects created inside schemas.

PostgreSQL allows setting default privileges so that new tables or objects inside a schema automatically grant permissions to certain users or roles. For example, ALTER DEFAULT PRIVILEGES IN SCHEMA sales GRANT SELECT ON TABLES TO user1; means any new table in sales will let user1 read it without extra grants.

Result

You can automate permission management for new objects inside schemas.

Knowing default privileges saves time and prevents forgetting to grant access on new objects.

6

Advanced

Combining Schema and Object Permissions

🤔Before reading on: can a user with SELECT on a table but no USAGE on its schema read the table? Commit to your answer.

Concept: Explore how schema-level and object-level permissions work together.

In PostgreSQL, a user must have USAGE on a schema to access objects inside it, even if they have permissions on the objects themselves. For example, if user1 has SELECT on sales.orders but no USAGE on sales schema, they cannot query orders. Both permissions are needed to access data.

Result

You understand that schema permissions gate access to all contained objects regardless of object permissions.

Knowing this prevents security holes where object permissions alone are not enough to grant access.

7

Expert

Security Implications and Best Practices

🤔Before reading on: do you think granting CREATE on a schema can lead to security risks? Commit to your answer.

Concept: Understand advanced security considerations and how to use schema-level control safely in production.

Granting CREATE on a schema lets users add new objects, which can be risky if they create malicious functions or tables. Best practice is to limit CREATE to trusted roles and regularly audit schema permissions. Also, combining schema-level control with row-level security and roles creates layered defense. Monitoring default privileges prevents accidental exposure.

Result

You can design secure, maintainable permission schemes using schema-level controls.

Understanding risks of schema permissions helps avoid serious security breaches in real systems.

Under the Hood

PostgreSQL stores permissions as access control lists (ACLs) linked to schemas and objects. When a user tries to access an object, the system checks if the user has USAGE on the schema and the required permission on the object. This two-step check ensures that schema-level control acts as a gatekeeper before object-level permissions are considered. Default privileges are stored separately and applied automatically when new objects are created.

Why designed this way?

This design separates namespace access (schema) from object access to provide flexible, fine-grained control. It allows administrators to quickly restrict or allow access to entire groups of objects without changing each object's permissions. Alternatives like only object-level permissions would be cumbersome and error-prone for large databases.

┌───────────────┐
│ User Request  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Check Schema  │
│   USAGE ACL   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Check Object  │
│ Permissions   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│  Access Data  │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does granting USAGE on a schema let a user read all tables inside? Commit yes or no.

Common Belief: Granting USAGE on a schema automatically lets users read all tables inside it.

Reality: USAGE on a schema only allows access to the schema namespace; users still need SELECT permission on each table to read data.

Why it matters: Assuming USAGE grants full access can lead to security holes where users get more access than intended.

Quick: If a user has SELECT on a table but no USAGE on its schema, can they query the table? Commit yes or no.

Common Belief: Having SELECT on a table is enough to read it, regardless of schema permissions.

Reality: Without USAGE on the schema, the user cannot access the table even if they have SELECT permission on it.

Why it matters: Ignoring schema permissions can cause confusion and unexpected access denials.

Quick: Does revoking CREATE on a schema remove access to existing tables? Commit yes or no.

Common Belief: Revoking CREATE on a schema removes access to all objects inside it.

Reality: Revoking CREATE only prevents creating new objects; it does not affect access to existing tables or views.

Why it matters: Misunderstanding this can lead to incorrect permission setups and security gaps.

Quick: Are default privileges applied retroactively to existing tables? Commit yes or no.

Common Belief: Setting default privileges changes permissions on all existing objects in the schema.

Reality: Default privileges only apply to new objects created after the setting; existing objects remain unchanged.

Why it matters: Assuming retroactive effect can cause unexpected access issues or security holes.

Expert Zone

1

Schema USAGE permission is required even if object-level permissions are granted, acting as a mandatory gatekeeper.

2

Default privileges must be carefully managed because they can unintentionally expose new objects to users.

3

Granting CREATE on schemas can allow users to create functions that execute with elevated privileges, posing security risks.

When NOT to use

Schema-level access control is not sufficient when you need to restrict access to specific rows or columns within tables; in those cases, use row-level security or column-level permissions. Also, for very dynamic permission needs, consider application-level controls or external authorization systems.

Production Patterns

In production, teams often create roles aligned with job functions and grant schema-level permissions to these roles. They combine schema-level control with object-level grants and default privileges to automate permission management. Regular audits and use of security extensions help maintain tight control.

Connections

Role-Based Access Control (RBAC)

Schema-level access control builds on RBAC by assigning permissions to roles that control schema access.

Understanding RBAC helps grasp how schema permissions are grouped and managed efficiently in teams.

File System Permissions

Schema-level access control is similar to folder permissions in file systems controlling access to all files inside.

Knowing file system permissions clarifies why controlling access at the container level is powerful and efficient.

Network Firewall Rules

Both schema-level access control and firewall rules act as gatekeepers controlling access to groups of resources.

Seeing schema permissions as a firewall for database objects helps understand layered security design.

Common Pitfalls

#1 Granting USAGE on schema and assuming users can read all tables inside.

Wrong approach:

GRANT USAGE ON SCHEMA sales TO user1;

Correct approach:

GRANT USAGE ON SCHEMA sales TO user1;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO user1;

Root cause:

Confusing schema access with object access leads to incomplete permission grants.

#2 Revoking CREATE on schema expecting to block all access to schema objects.

Wrong approach:

REVOKE CREATE ON SCHEMA sales FROM user1;

Correct approach:

REVOKE USAGE ON SCHEMA sales FROM user1;

Root cause:

Misunderstanding that CREATE controls only object creation, not access.

#3 Setting default privileges but forgetting they apply only to new objects.

Wrong approach:

ALTER DEFAULT PRIVILEGES IN SCHEMA sales GRANT SELECT ON TABLES TO user1;

Correct approach:

ALTER DEFAULT PRIVILEGES IN SCHEMA sales GRANT SELECT ON TABLES TO user1;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO user1;

Root cause:

Assuming default privileges affect existing objects causes unexpected permission gaps.

Key Takeaways

Schema-level access control manages permissions on a group of database objects by controlling access to their container, the schema.

Granting USAGE on a schema allows users to access the schema namespace but does not automatically grant rights on objects inside.

Both schema-level and object-level permissions are required for users to fully access data within schemas.

Default privileges automate permission grants for new objects but do not affect existing ones.

Careful management of schema permissions, especially CREATE rights, is crucial to maintain database security.

Practice

1. What does the USAGE privilege on a schema in PostgreSQL allow a user to do?

easy

A. Access objects within the schema without creating new ones

B. Create new tables and objects inside the schema

C. Delete the schema entirely

D. Modify data in tables outside the schema

Solution

1. Step 1: Understand USAGE privilege meaning

   The USAGE privilege allows a user to access objects inside the schema, such as selecting data from tables, but does not allow creating new objects.

2. Step 2: Differentiate from CREATE privilege

   The CREATE privilege is needed to add new tables or other objects. USAGE alone does not grant this ability.

3. Final Answer:

   Access objects within the schema without creating new ones -> Option A

4. Quick Check:

   USAGE = access only [OK]

Hint: USAGE lets you use, CREATE lets you add [OK]

Common Mistakes:

• Confusing USAGE with CREATE privilege
• Thinking USAGE allows schema deletion
• Assuming USAGE grants data modification outside schema

2. Which of the following is the correct syntax to grant CREATE privilege on a schema named sales to user alice?

easy

A. GRANT CREATE ON sales TO alice;

B. GRANT CREATE TO alice ON SCHEMA sales;

C. GRANT CREATE ON SCHEMA sales TO alice;

D. GRANT CREATE ON DATABASE sales TO alice;

Solution

1. Step 1: Identify correct GRANT syntax for schema

   In PostgreSQL, to grant privileges on a schema, the syntax is: GRANT privilege ON SCHEMA schema_name TO user;

2. Step 2: Match syntax with options

   GRANT CREATE ON SCHEMA sales TO alice; matches this syntax exactly: GRANT CREATE ON SCHEMA sales TO alice;

3. Final Answer:

   GRANT CREATE ON SCHEMA sales TO alice; -> Option C

4. Quick Check:

   GRANT ... ON SCHEMA ... TO ... [OK]

Hint: Use 'ON SCHEMA' when granting schema privileges [OK]

Common Mistakes:

• Omitting 'SCHEMA' keyword
• Using 'ON DATABASE' instead of 'ON SCHEMA'
• Placing TO clause incorrectly

3. Given the commands below, what will be the result of SELECT * FROM sales.orders; when run by user bob?

GRANT USAGE ON SCHEMA sales TO bob;
REVOKE CREATE ON SCHEMA sales FROM bob;

medium

A. Query runs but returns no rows

B. Error: permission denied for schema sales

C. Error: relation sales.orders does not exist

D. Query runs successfully and returns rows from sales.orders

Solution

1. Step 1: Analyze granted privileges

   User bob has USAGE on schema sales, so can access objects inside it. CREATE privilege is revoked, so bob cannot create new objects but can read existing ones.

2. Step 2: Understand effect on SELECT query

   Since bob has USAGE, SELECT on sales.orders will work if bob has SELECT privilege on the table (assumed). The REVOKE of CREATE does not affect SELECT.

3. Final Answer:

   Query runs successfully and returns rows from sales.orders -> Option D

4. Quick Check:

   USAGE allows access, REVOKE CREATE blocks creation only [OK]

Hint: USAGE lets you read; CREATE controls adding objects [OK]

Common Mistakes:

• Confusing CREATE with SELECT privilege
• Assuming REVOKE CREATE blocks all access
• Ignoring USAGE privilege effect

4. You want to allow user carol to create tables in schema inventory, but she gets an error: permission denied for schema inventory. Which command fixes this?

medium

A. GRANT CREATE ON SCHEMA inventory TO carol;

B. GRANT USAGE ON SCHEMA inventory TO carol;

C. GRANT ALL PRIVILEGES ON SCHEMA inventory TO carol;

D. REVOKE USAGE ON SCHEMA inventory FROM carol;

Solution

1. Step 1: Understand error cause

   To create tables, user needs both USAGE and CREATE privileges on the schema. Without USAGE, permission denied error occurs.

2. Step 2: Grant missing privilege

   Granting USAGE on schema inventory to carol allows her to access the schema and create tables if CREATE is already granted.

3. Final Answer:

   GRANT USAGE ON SCHEMA inventory TO carol; -> Option B

4. Quick Check:

   USAGE needed before CREATE works [OK]

Hint: Grant USAGE before CREATE to avoid permission errors [OK]

Common Mistakes:

• Granting CREATE without USAGE privilege
• Revoking instead of granting privileges
• Assuming ALL PRIVILEGES always needed

5. You want to restrict user dave so he can only create objects in schema projects but cannot access any existing objects. Which combination of privileges achieves this?

hard

A. GRANT CREATE ON SCHEMA projects TO dave; REVOKE USAGE ON SCHEMA projects FROM dave;

B. GRANT USAGE ON SCHEMA projects TO dave; REVOKE CREATE ON SCHEMA projects FROM dave;

C. GRANT ALL ON SCHEMA projects TO dave;

D. REVOKE ALL ON SCHEMA projects FROM dave;

Solution

1. Step 1: Understand privilege effects

   CREATE allows adding new objects. USAGE allows accessing existing objects. To restrict access but allow creation, grant CREATE and revoke USAGE.

2. Step 2: Apply correct commands

   GRANT CREATE ON SCHEMA projects TO dave; REVOKE USAGE ON SCHEMA projects FROM dave; grants CREATE and revokes USAGE, so dave can create but not access existing objects.

3. Final Answer:

   GRANT CREATE ON SCHEMA projects TO dave; REVOKE USAGE ON SCHEMA projects FROM dave; -> Option A

4. Quick Check:

   Create without usage blocks access [OK]

Hint: Grant CREATE, revoke USAGE to allow creation only [OK]

Common Mistakes:

• Granting USAGE allows access to existing objects
• Revoking CREATE disables creation
• Granting ALL gives too many rights