Schema-level access control in PostgreSQL - Time & Space Complexity

Time Complexity: Schema-level access control

O(log n)

Understanding Time Complexity

We want to understand how the time to check permissions grows when controlling access to database schemas.

How does the system handle more users or schemas when deciding who can do what?

Scenario Under Consideration

Analyze the time complexity of this schema-level access control check.

-- Check if user has USAGE privilege on a schema
SELECT has_schema_privilege('username', 'schema_name', 'USAGE');

-- Grant USAGE privilege on schema to a user
GRANT USAGE ON SCHEMA schema_name TO username;

-- Revoke USAGE privilege on schema from a user
REVOKE USAGE ON SCHEMA schema_name FROM username;
    

This code checks and manages user permissions on a schema in PostgreSQL.

Identify Repeating Operations

Look for repeated checks or scans when verifying access.

• Primary operation: Checking user privileges involves looking up entries in system tables that store permissions.
• How many times: Once per access check, but can happen many times if many users or schemas exist.

How Execution Grows With Input

As the number of users or schemas grows, the system must search through more privilege entries.

Input Size (n)	Approx. Operations
10 users/schemas	Few lookups, very fast
100 users/schemas	More lookups, still quick due to indexing
1000 users/schemas	More entries to check, but indexes keep it efficient

Pattern observation: The time grows slowly because the database uses indexes to find privileges quickly.

Final Time Complexity

Time Complexity: O(log n)

This means checking schema privileges takes time that grows slowly as the number of users or schemas increases, thanks to indexing.

Common Mistake

[X] Wrong: "Checking schema privileges takes the same time no matter how many users or schemas exist."

[OK] Correct: The system must search through privilege data, so more users or schemas mean more data to check, but indexes help keep it efficient.

Interview Connect

Understanding how access control scales helps you explain how databases keep data safe without slowing down as they grow.

Self-Check

"What if we added caching for privilege checks? How would the time complexity change?"