How to Configure Greenplum Column Level Security

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up greenplum column level security by assigning table privileges or building filtered views. Database admins revoke broad table rights and grant read rights only on safe columns. Most data teams start by giving analysts full read rights on whole schemas. When permissions are too broad, a single query can pull emails and card numbers. How greenplum column level security works Greenplum builds on standard relational role privileges to limit what users can read. First, an admin removes d

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

You set up greenplum column level security by assigning table privileges or building filtered views. Database admins revoke broad table rights and grant read rights only on safe columns. Most data teams start by giving analysts full read rights on whole schemas. When permissions are too broad, a single query can pull emails and card numbers.

How greenplum column level security works

Greenplum builds on standard relational role privileges to limit what users can read. First, an admin removes default read rights from the public role on a table. Next, the admin creates a specific role for a team, like marketing or finance. The admin then grants read rights only on the exact columns that role needs. If a user runs a query that asks for a blocked column, it fails. The database checks the role list before it reads any data from the segments.

Many teams also use database views to manage column rules across large analytics clusters. In this design, the admin hides the base table from normal users completely. The admin builds a view that selects only the safe columns from the base table. Users get permission to read the view, not the raw table underneath it. This approach works well when teams need to join several tables together for reports. Admins can also apply simple text functions inside the view to rewrite sensitive values.

Using roles and views to restrict columns

Managing column grants directly on tables works well when you have a few tables. As your warehouse grows to hundreds of tables, direct column grants become hard to track. Every time an engineer adds a new column, someone must update the role grants. If an admin forgets a grant, broken dashboards and failed jobs pile up fast. If an admin grants full table rights to save time, private data sits open.

Views help group these rules, but they bring their own upkeep cost over time. Teams often end up creating five or six views for a single base table. One view serves finance, another serves support, and a third serves product analysts. When the base table changes, the data team must fix all those views together. Also, users who write ad hoc queries cannot use wildcard selects on restricted tables. They must type out every allowed column name in every single query they run.

Column-Level Encryption + End-to-End Encryption: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native column grants solve and what they miss

Native column grants and views do a great job of blocking unauthorized read queries. They ensure that a standard reporting role cannot pull columns outside its assigned scope. They also keep rules inside the database catalog, so every SQL client obeys them. For static reporting apps with fixed queries, native grants handle the basic access needs.

Still, static grants only decide who logs in and which columns a role owns. They do not inspect what individual statements do in flight during an active session. If a developer needs production access to fix a bug, static roles fall short. Giving that developer a privileged role exposes every phone number and national ID inside. Native grants cannot rewrite matched fields in results on the fly without extra views. Also, shared app roles cannot tie a specific column read back to one person.

Add runtime guardrails and masking beside your databases and APIs

Login rules, role grants, and CI checks govern who connects to your stack. Yet any allowed script, engineer, or AI agent still runs commands with the full scope of its role. For workloads on Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes, SSH, or internal HTTP services, hoop.dev sits inline in the protocol beside the service as an open-source proxy, a sidecar for your database or API. On every production query an agent or human runs, hoop.dev decides whether to pass, mask, block, or escalate it for review.

Before a query runs, hoop.dev guardrails check the statement against your rules, an AI Analyzer you configure can score intent or hold risky calls for human approval, and hoop.dev data masking rewrites sensitive fields in responses in memory. Explore the MIT-licensed project on GitHub.

Frequently asked questions

Can users run a select all query on a table with column grants?

No, a select all query fails if the user lacks rights on any column. The user must list only the permitted column names in the query text.

Should we use column grants or views to hide sensitive fields?

Use direct column grants when you only need to lock down a few fields. Use views when you want users to run simple queries without permission errors.

How do column grants affect query speed in Greenplum?

Column grants check permissions during query planning and do not slow down data scans. Views also run fast unless they include heavy functions on millions of rows.

The query runs. The emails stay hidden.