Table-level permissions in PostgreSQL - Time & Space Complexity

Time Complexity: Table-level permissions

O(n)

Understanding Time Complexity

We want to understand how the time it takes to check table-level permissions changes as the number of tables grows.

How does the system handle permission checks when many tables exist?

Scenario Under Consideration

Analyze the time complexity of this permission check query.

-- Check if user has SELECT permission on a table
SELECT has_table_privilege('username', 'schema.table_name', 'SELECT');

-- Or list all tables user can SELECT from
SELECT tablename
FROM pg_tables
WHERE has_table_privilege('username', schemaname || '.' || tablename, 'SELECT');
    

This code checks if a user has SELECT permission on one or many tables.

Identify Repeating Operations

Look for repeated checks or loops.

• Primary operation: Checking permission for each table using has_table_privilege.
• How many times: Once per table when listing all tables.

How Execution Grows With Input

As the number of tables grows, the system checks permissions for each table separately.

Input Size (n)	Approx. Operations
10	10 permission checks
100	100 permission checks
1000	1000 permission checks

Pattern observation: The number of permission checks grows directly with the number of tables.

Final Time Complexity

Time Complexity: O(n)

This means the time to check permissions grows in a straight line as the number of tables increases.

Common Mistake

[X] Wrong: "Checking permissions on many tables happens instantly regardless of how many tables exist."

[OK] Correct: Each table requires a separate permission check, so more tables mean more work and more time.

Interview Connect

Understanding how permission checks scale helps you design systems that stay fast even as data grows.

Self-Check

"What if we cached permission results for tables? How would the time complexity change when checking permissions repeatedly?"