Right, let’s talk about making your data lie to people. It sounds nefariousous, but I promise it’s for a good cause: security. You don’t want your intern running a SELECT * FROM users; and walking away with the CEO’s hashed password and everyone’s home address. The old way to solve this was a mess of duplicated, one-off views for every conceivable permission level. It was a nightmare to maintain. Thankfully, modern databases give us tools to build a single, intelligent view that presents different data to different people. It’s like a bouncer for your rows and a privacy filter for your columns.

We’ll tackle this in two parts: row-level security (filtering which rows you see) and column masking (obscuring or altering specific column values). The magic sauce that makes this work is almost always the current_user or session_user function, which tells the database who’s asking the question.

The Basics: A Simple Security View

Let’s say we have a sales.orders table. The requirement is that salespeople can only see their own orders. The brute force method is creating a view for each user. Don’t do that. You’re better than that.

Instead, we create a single view that uses a WHERE clause tied to the current user.

CREATE VIEW sales.my_orders AS
SELECT order_id, customer_id, order_total, status
FROM sales.orders
WHERE salesperson_username = current_user;

Now, when user emma queries SELECT * FROM sales.my_orders;, it’s logically equivalent to ...WHERE salesperson_username = 'emma';. It’s elegant, simple, and effective. The key here is that the view owner must have permissions on the underlying table, but the user querying the view only needs permission on the view itself. The database engine seamlessly adds that WHERE clause during execution.

Leveling Up: Parameterized Row Security

What if your security logic isn’t based on a direct username match? Maybe you have a separate table mapping users to regions. This is where the view gets powerful.

CREATE VIEW sales.regional_orders AS
SELECT o.order_id, o.customer_id, o.order_total, o.status
FROM sales.orders o
JOIN sales.user_regions ur ON ur.region_id = o.region_id
WHERE ur.username = current_user;

The view doesn’t care how complex the joining logic gets. It executes the entire query, and the result set is automatically filtered for the current user. The pitfall here? Performance. That join happens every time. If your user_regions table is small, it’s fine. If it’s huge, you’ll want indexes on the join columns. This isn’t a flaw of the view; it’s just basic SQL tuning.

Now, Let’s Hide Some Columns (Column Masking)

Row filtering is one thing, but sometimes you need to show a column but not its actual value. Enter column masking. The simplest form is using a CASE statement within the view. Imagine a users table where you want to hide email addresses from everyone except admins.

CREATE VIEW public.user_profiles AS
SELECT
    user_id,
    username,
    -- The magic happens here:
    CASE
        WHEN current_user = 'admin' THEN email
        ELSE '***REDACTED***'
    END AS email,
    date_joined
FROM public.users;

This is brilliantly straightforward. When the admin queries the view, they see the real data. Everyone else gets a polite “mind your own business” message. You can get fancier, showing only part of the data. A common pattern for credit cards or phone numbers:

    CASE
        WHEN is_admin() THEN phone_number -- assuming a helper function
        ELSE '***-***-' || RIGHT(phone_number, 4)
    END AS phone_number

The Granular Approach: SECURITY DEFINER and SECURITY INVOKER

This is where PostgreSQL and other advanced databases show their teeth. When you create a view, you can define its security context. This is a crucial decision.

  • SECURITY INVOKER (the default): The view runs with the permissions of the user querying the view. If they don’t have access to the underlying tables, the view will fail. This is the safe, conservative choice.
  • SECURITY DEFINER: The view runs with the permissions of the user who created the view. This is incredibly powerful and incredibly dangerous. It means you can grant a user access to the view while withholding access from the underlying table. The view acts as a trusted gatekeeper.
CREATE VIEW sales.secure_orders WITH (security_definer) AS
SELECT * FROM sales.orders
WHERE salesperson_username = current_user;

Why is this dangerous? Because inside the view, you’re essentially the super-user who created it. You must write your WHERE clauses perfectly. A mistake could lead to a massive data leak. I use SECURITY DEFINER sparingly, only when absolutely necessary to abstract away complex underlying schemas. Always, always test these views exhaustively.

The Gotchas and Best Practices

  1. Performance is Your Problem: The database will optimize the query behind the view, but it’s still adding a predicate. If your underlying WHERE salesperson_username = current_user can’t use an index, the view will be slow. This isn’t the view’s fault; it’s your job to index appropriately.
  2. Nesting Views is a Trap: You can create a view that selects from another security view. Be very careful. The rules compound, and debugging a performance problem or a security leak through three layers of views is a special kind of hell. Keep the chain short.
  3. Test All Roles: The single most important step. Don’t just test as an admin. Script your tests: connect as user_a, user_b, and readonly_user and verify they each see exactly what they should see, and nothing more. Automate this.
  4. Document the Logic: That CASE statement hiding emails? Put a comment right there in the view definition SQL explaining the business rule. The next person (which might be you in six months) will thank you.

These techniques transform your database from a passive data dump into an active, security-aware participant. It moves access control closer to the data, which is almost always where it belongs. Just remember, with great power (like SECURITY DEFINER) comes great responsibility. Don’t screw it up.