Column-Level Access Control in PostgreSQL with Pgcli

The query hit the database like a bullet. Pgcli lit up your terminal, results streaming in fast, clean, and colored. But the truth sat in plain sight: the table contained columns that not every user should see. Column-level access in PostgreSQL is no joke, and Pgcli handles it with sharp precision when configured right.

PostgreSQL lets you strip visibility down to individual columns. This means you can give one role access to the “name” and “email” fields while hiding “salary” and “ssn” entirely. Pgcli, the popular command-line client for Postgres, works seamlessly with those rules. If PostgreSQL denies a column to a user, Pgcli will never return it—whether you run SELECT * or specify individual fields.

To implement column-level access, start where it matters: PostgreSQL roles and permissions. Use GRANT and REVOKE commands to control who can read or write each column. For example:

REVOKE SELECT ON TABLE employees FROM analyst_role;
GRANT SELECT (name, email) ON employees TO analyst_role;

From there, Pgcli doesn’t need any special configuration—it respects PostgreSQL security boundaries by default. This keeps sensitive data invisible without breaking workflows. Queries, auto-completion, and table previews only show what the current role can access.

The benefits stack up fast. Performance stays solid because hidden columns aren’t fetched. Security improves because application and CLI users are bound to the same rules. Auditing gets simpler: what’s off-limits in the database stays off-limits everywhere, including your favorite terminal.

One trap to avoid: relying on application logic alone. Enforce column-level permissions in PostgreSQL itself, so Pgcli and any other client can’t bypass them. Pair this with pg_hba.conf role restriction and schema design that groups sensitive columns logically.

Done right, Pgcli column-level access is pure leverage—fine control without friction, enforced at the source. Roles see exactly what they should see, nothing more. The terminal stays clean, safe, and ready for work.

Test it live in minutes with hoop.dev. Spin up a secure Postgres instance, enable column-level access, connect with Pgcli, and watch the rules hold under real queries.