Implementing Column-Level Permissions in Postgres

Postgres has a built-in answer for this: GRANT accepts a column list, so SELECT, INSERT, UPDATE, and REFERENCES can all be scoped down to specific columns instead of the whole table. DELETE doesn't get this treatment, and for a simple reason: deleting a row deletes all of it, so there's no such thing as a partial delete at the column level. What "works" hides is a handful of behaviors that trip people up the moment they try to use it for anything beyond a textbook example, and that's the ground this piece covers.

The basic column-level GRANT and the SELECT * trap

The syntax itself is simple. Running GRANT SELECT (id, full_name, email, department, hire_date, manager_id) ON employees TO hr_analyst withholds salary_cents and national_id from hr_analyst.

The trouble starts the moment someone (or some ORM) writes SELECT * instead. The instant one column is withheld, the whole query gets rejected, not just the missing column.

The error Postgres returns says "permission denied for table employees," which actually wastes people's time." The column-level restriction is working exactly as designed. It's just that the error message gives no hint of that.

The practical fallout is bigger than a confusing afternoon of debugging. Any application or ORM that emits SELECT * has to be rewritten to name columns explicitly before column-level grants can go into production. Column-level grants only start paying off once that cleanup is done, and that cleanup usually takes more effort than setting up the grants themselves.

The union rule: why revoking a column from a table-level grant does nothing

The single most common mistake with column privileges has nothing to do with syntax errors. It's a misunderstanding of how Postgres combines table-level and column-level grants.

The rule: a role can access a column if it holds the privilege at the table level, or at the column level, whichever is broader. Table-level grants are a floor, not a ceiling that column grants carve pieces out of. They're a floor, and column grants only ever add to it, never subtract.

This goes wrong in practice when a team runs GRANT SELECT ON employees TO hr_analyst, then later decides to tighten things up and runs GRANT SELECT (full_name) ON employees TO hr_analyst, expecting that second statement to restrict access down to just full_name. The hr_analyst role still reads every column on the table, salary_cents and national_id included, because the original table-level grant is still standing. The second grant added nothing; it was already covered by the first.

There's no command that subtracts from a table-level grant on a column-by-column basis, either. Running REVOKE SELECT (salary_cents) ON employees FROM hr_analyst only removes a column-level grant if one existed. It does not touch the broader table-level grant, so the role keeps reading salary_cents regardless.

The only sequence that actually works is to start from zero. Revoke the table-level grant first, with REVOKE SELECT ON employees FROM hr_analyst, and only then grant back the specific columns the role actually needs. Trying to carve restrictions out of a broad grant doesn't work, no matter how it's phrased.

There's a second version of this same trap, and it's arguably worse because it's invisible until someone goes looking. If GRANT SELECT ON employees TO PUBLIC was ever run, every role in the database inherits that grant automatically, and all the careful column-level work becomes decorative. The fix is to run REVOKE ALL ON employees FROM PUBLIC before setting up anything else, so there's no silent table-level floor sitting underneath the column grants.

What makes both of these mistakes dangerous rather than just annoying is how they fail. The system looks secured and isn't, and nobody finds out until someone queries salary_cents and gets data back that was supposed to be hidden.

Write-path grants and the WHERE clause SELECT requirement

Everything so far has been about reading data. Column grants on the write side stop someone from changing a value they shouldn't touch.

Take a billing service that needs to update a customer's plan but should never be able to touch their account balance. Granting UPDATE (plan) to a billing_service role while withholding UPDATE (credit_balance) does exactly that: the service can change plan values all day, and any attempt to touch credit_balance gets rejected, even if the code tries. This is the write-path case where column-level grants genuinely earn their place in a production schema. It's a deliberate, intentional guardrail, not a workaround for a syntax quirk. It's a direct, intentional guardrail against a specific category of mistake or compromise.

A role doing updates almost always needs to read more columns than it's allowed to write. The billing_service role has to see credit_balance to decide whether an update makes sense in context, even though it can never change that value. That means SELECT access usually needs to be granted more broadly than UPDATE access, and the reason should be written down so the next engineer who looks at the grants doesn't assume it's a mistake.

INSERT carries its own version of the same logic, with a sharper edge. The fix is straightforward: set a default on any NOT NULL column that's being withheld, for example ALTER TABLE employees ALTER COLUMN salary_cents SET DEFAULT 0. Do that ahead of time, and the insert path keeps working the way everyone expects.

Security-invoker views as the workaround for ORMs and BI tools that emit SELECT *

For teams stuck with a BI tool or an ORM that can't be rewritten to enumerate columns, views are the escape hatch. Nothing needs to change on the consumer side at all.

That's why views, not raw column grants, end up being the default choice whenever the thing querying the data is something a team doesn't control. Patching every BI connector and every ORM call site to name columns explicitly is a lot of work for marginal gain when a single view does the same job.

But the safety of this approach depends entirely on one setting, and it's easy to get wrong. By default, a view runs with the privileges of whoever owns it, not whoever is querying it. That means the view applies the owner's row-level security policies on the underlying table, and if the owner happens to be a superuser or holds BYPASSRLS, the view can bypass row-level security entirely for every caller who queries it. A view owned by a more privileged role than the person querying it hands out access quietly. It's a path to privilege escalation, quietly handing out access the caller was never supposed to have.

Postgres 15 added a fix: WITH (security_invoker = true), set on the view itself, makes the view run with the privileges of whoever is actually querying it, not the owner. With that option set, row-level security policies on the base table apply the way anyone would expect them to.

This isn't settled, finished business, though. A January 2026 thread on the pgsql-hackers mailing list, opened by Supabase's Steve Chavez, raised exactly this concern: views aren't secure by default, because they bypass row-level security unless security_invoker is explicitly turned on, and that setting is easy to forget on any new view someone creates. Cybertec's Laurenz Albe, replying in the same thread, made a related point: restricting columns through views and restricting rows through RLS are two separate, legitimate jobs, not substitutes for each other, and a given table might need both running at once. Views solve the SELECT * problem cleanly. They don't solve it safely unless security_invoker gets set every single time, and teams keep forgetting to set it.

What column grants and views both fail to protect

Column grants and views both control who can read a value. Neither one hides the fact that the column exists. Any role, restricted or not, can query information_schema.columns and see that salary_cents is right there on the employees table, see its data type, see its default value. If the column name itself is sensitive information, column grants can't address that; the answer is a view that omits the column from its definition entirely, or a separate table altogether, since a grant only blocks access to the value.

There's a subtler leak: a role with no access to a column can sometimes learn whether a specific value already exists in it, just by trying to insert that value and watching for a constraint violation. That's a real gap when the mere presence of a value needs to stay confidential, not just its contents.

Functions are another blind spot. A SECURITY DEFINER function owned by a privileged role reads with that role's privileges, not the caller's, so it can return any column on a table regardless of what the calling role was actually granted. Any audit of column grants needs to include a look at these functions, since they sit outside the grant system.

None of this makes column-level restriction a waste of effort. It's a real control with a specific, limited job: it restricts value access, not existence, not every indirect way a value's presence might leak. CVE-2021-20229 is a useful reminder of how granular these gaps can get: a user holding SELECT on a single column was able to construct a query that returned every column on the table, because stored views using column-level privileges had incomplete column-usage bitmaps, and the fix recommended for anyone depending on column-level permissions for security was to run CREATE OR REPLACE on every user-defined view to force it to reparse. Column grants work. They just don't work alone.

Dynamic data masking via extensions when hiding values rather than columns is enough

Some use cases just need the value obscured rather than the column locked away. They need the value obscured while the column itself stays queryable, which is a different problem with a different set of tools, mostly extensions rather than native grants.

postgresql_anonymizer, known as pg_anon, released version 2.3 in July 2025. That release added a replica masking mechanism, letting a DBA keep a masked clone synced to production using Postgres's own logical replication. Its dynamic masking mode masks data for specified roles without ever altering the underlying rows, so a query from a masked role gets anonymized results back in place, while the real data sits untouched underneath. Stabilization work aimed at version 3.0 is planned for early 2026.

Amazon took a similar approach inside Aurora. Amazon frames it explicitly as a compliance tool, built to help meet GDPR, HIPAA, and PCI DSS requirements.

Both of these are useful for the same reason: the underlying data never leaves, never gets deleted, never gets locked out from the people who legitimately need the real values for audits or compliance checks. That's also their limit. A masked view protects against accidental exposure, a dashboard showing a column it shouldn't, an analyst stumbling across a value by accident. It's not a defense against someone determined to get at the real data, because the real data is still sitting right there, one role switch away.

Operational risks that outlive the initial setup: migrations, defaults, and audit

The most common way a correct setup turns into a production problem months later has nothing to do with the grants themselves. New tables created by a migration don't inherit column-level grants automatically, and without ALTER DEFAULT PRIVILEGES configured ahead of time, every migration that creates a table leaves it wide open, no grants for any other role, until someone happens to notice. The fix has to be set up once, in advance, as a default privilege rule, because there's no way to catch this after the fact except by checking every new table by hand.

PUBLIC keeps causing the same problem. Any future GRANT SELECT ON

TO PUBLIC, run by anyone, at any point, silently overrides every column-level restriction already in place. A policy of auditing PUBLIC grants on sensitive tables on a schedule catches this before it becomes an incident instead of after.

And column grants leave row-level access completely uncontrolled. A setup that restricts salary columns but applies no row policy still allows a restricted role to count rows in another department's headcount, leaking aggregate information. Treating one as a substitute for the other leaves a real gap.

Where column-level control fits in a self-serve analytics stack

BI tools exist to give analysts broad, flexible access to query data however a dashboard needs it. Production databases hold columns that are visible only to a handful of approved roles.

A BI tool that reimplements column filtering in its own permission system, separate from the database, is one misconfiguration away from a leak, since nothing stops a user from connecting around it.

A different argument comes from semantic-layer tools like Cube or LookML-style platforms: defining column access per-dashboard is fragile and hard to keep consistent, and centralizing that logic in a governed semantic layer does something native Postgres grants can't do on their own, it shapes which columns people should be using, not just which ones they're technically allowed to touch.

Both arguments are right, just about different layers of the same stack. Database-level column grants set the floor, the restriction that holds no matter what tool is pointed at the database. A semantic or BI layer builds governance and usability on top of that floor, but it doesn't replace it, because the floor has to exist independently of whatever sits above it.

That's the real question to ask when evaluating any BI or internal-tools platform: do permissions follow the data to every surface it's queried from, row-level security, column-level security, multi-tenant isolation, governance that travels with the model, rather than access rules rebuilt separately inside each dashboard. A database-native BI platform that connects straight to Postgres and enforces row-level security at the database layer picks up any column grants already in place automatically, since it's querying through the same privilege system everyone else is using.

AI-generated SQL raises the stakes on all of this. A natural-language interface that turns a plain-English question into a SQL query has to run through that same database privilege system, not some separate permission layer bolted on for convenience. Column grants that only apply to hand-written queries stop being enough the moment someone can just describe what they want in a sentence and let a model write the SQL for them.