What Does $user Mean in PostgreSQL’s search_path?

If you’ve worked with PostgreSQL long enough, you’ve probably seen something like this:

SHOW search_path;

And the result looked like:

"$user", public

At first glance, $user feels mysterious.

Is it a variable? A placeholder? A real schema?

Let’s unpack what $user actually means, how PostgreSQL resolves it, and why it exists in the first place.

---

The Role of search_path in PostgreSQL

Before diving into $user, we need to understand what search_path does.

search_path defines the order in which PostgreSQL looks for objects (tables, views, functions, etc.) when you reference them without a schema name.

For example:

SELECT * FROM orders;

PostgreSQL resolves orders by checking schemas in order, as listed in search_path.

---

What Is $user in search_path?

$user is not a schema itself.

See also: Mastering the Linux Command Line — Your Complete Free Training Guide

Instead, it’s a special placeholder that PostgreSQL resolves at runtime to the name of the current database role.

In other words:

"$user"

means:

“Look for a schema with the same name as the current user.”

---

How PostgreSQL Resolves $user

The resolution process is straightforward but strict.

Step-by-step resolution

1. PostgreSQL retrieves the current role name SELECT current_user;
2. It checks whether a schema with the same name exists SELECT schema_name FROM information_schema.schemata WHERE schema_name = current_user;
3. If the schema exists:
   • $user expands to that schema
   • It becomes the first schema searched
4. If the schema does not exist:
   • $user is silently skipped
   • PostgreSQL moves on to the next schema in search_path

No error is raised. The placeholder is simply ignored.

---

A Concrete Example

Setup

CREATE ROLE alice LOGIN;
CREATE SCHEMA alice AUTHORIZATION alice;

Now log in as alice.

SHOW search_path;

Result:

"$user", public

What PostgreSQL actually searches

Internally, this becomes:

alice, public

So when alice runs:

SELECT * FROM customers;

PostgreSQL searches:

1. alice.customers
2. public.customers

in that exact order.

---

What If the User Schema Doesn’t Exist?

Let’s say user bob exists, but there’s no bob schema.

CREATE ROLE bob LOGIN;
-- no CREATE SCHEMA bob;

Logged in as bob:

SHOW search_path;

Still shows:

"$user", public

But at runtime, PostgreSQL resolves this as:

public

$user disappears because bob schema doesn’t exist.

---

Why $user Exists at All

The $user placeholder enables a powerful design pattern:

Per-user private schemas

Each user can have:

• Their own schema
• Their own tables
• Their own views
• Their own temporary overrides

All without changing application SQL.

This allows:

• Multi-tenant isolation
• Safer defaults
• Cleaner object ownership
• Minimal schema qualification

Historically, this pattern was especially popular in shared database environments and early PostgreSQL deployments.

---

Security Implications (Important!)

Because search_path affects object resolution, it can also affect security.

The risk

If an attacker can create objects in a schema that appears early in search_path, they may:

• Override functions
• Shadow tables
• Hijack function calls

Best practice for security-sensitive code

For:

• Extensions
• SECURITY DEFINER functions
• Administrative scripts

Always set an explicit search_path:

SET search_path = pg_catalog, public;

Or inside a function:

CREATE FUNCTION secure_fn()
RETURNS void
SET search_path = pg_catalog
AS $$
BEGIN
  -- safe code
END;
$$ LANGUAGE plpgsql;

---

$user vs current_user

They are related, but not interchangeable.

Concept	Meaning
$user	Placeholder in search_path
current_user	SQL expression returning active role
session_user	Role that originally logged in

$user always resolves to current_user, not session_user.

---

When Should You Use $user?

Good use cases

• Developer sandboxes
• Multi-user shared databases
• Isolated per-user environments
• Education and training systems

Avoid in production when

• Running SECURITY DEFINER functions
• Writing extensions
• Managing critical system schemas

---

Key Takeaways

• $user is a runtime placeholder, not a schema
• It expands to the current role name
• PostgreSQL only uses it if a matching schema exists
• Otherwise, it’s silently skipped
• It enables per-user schema isolation
• It can introduce security risks if misused

Once you understand $user, search_path stops being magical—and starts being predictable.

And in databases, predictability is everything.