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.