How to Configure Greenplum Revoke Access

Published
Length
3 min read

Luis Felipe Dias

All posts

You run a greenplum revoke access task by removing role rights at the database, schema, and table levels. You must also strip default public group rights so users lose their backdoor read paths. Most data teams grant broad role rights when they set up a new cluster. Over time, these wide permissions let too many people read or change core data. Cleaning up these rules takes a clear step-by-step plan. How the greenplum revoke access model works Greenplum uses a role system built on PostgreSQL

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 run a greenplum revoke access task by removing role rights at the database, schema, and table levels. You must also strip default public group rights so users lose their backdoor read paths. Most data teams grant broad role rights when they set up a new cluster. Over time, these wide permissions let too many people read or change core data. Cleaning up these rules takes a clear step-by-step plan.

How the greenplum revoke access model works

Greenplum uses a role system built on PostgreSQL to manage both single users and groups. Every role can hold direct rights on objects or gain rights from parent group roles. When you remove a permission from one user, the database only deletes that direct grant. If the user belongs to a parent group role, they still keep the group rights. You must trace the full role tree before you start removing any permissions.

The coordinator node stores the main system catalog for all roles and permissions. When an admin changes role rights, the coordinator updates its catalog and syncs the segment nodes. You have to check three main places when you strip rights from a role. First, check the database level to stop the role from making new connections or temp tables. Second, check the schema level to stop the role from reading or adding objects inside that space. Third, check individual tables, views, and functions where an admin may have granted direct rights.

Why public roles and object ownership block cleanup

Many admins run a revoke command and find that the user can still query the data. The built-in public group is the most common reason for this issue. Every role in the cluster belongs to the public group by default. If the public group has usage rights on a schema, every user gets that same right. You must remove rights from the public group first, then grant rights back to specific teams.

Object ownership is the second big hurdle during a permission cleanup. The role that creates a table becomes its owner and holds full rights over it forever. Standard revoke rules do not strip permissions from the owner of a table or view. Before you remove a role or drop its rights, you must reassign its objects to a service role. You also need to update default privileges so future tables do not inherit the old rules.

Customer Support Access to Production: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native role changes solve and what they miss

Native permission cleanup locks down the cluster catalog and stops unauthorized roles from logging in. It ensures that each team only touches the schemas and tables needed for their daily work. Still, catalog rules only check who logs in and which whole tables they can read. They do not inspect individual queries in flight or control what happens inside an active session.

When you change role rights in the catalog, open database sessions often keep running. An engineer with a live session can still run heavy queries until you kill that process. Also, a user who needs table access for debugging gets full view of every column by default. Native grants cannot rewrite sensitive fields like emails, phone numbers, card numbers, or national IDs in results. Teams still face the risk of accidental table drops or mass data exports from valid accounts.

Check database and API queries on the wire

Identity tools, roles, and network rules decide who opens a session. Setup alone cannot check what each query does or mask sensitive values in a response once a session starts. When workflows touch Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes APIs, SSH, or internal HTTP APIs, hoop.dev sits inline in the wire protocol beside those services as an open-source proxy, a sidecar for your Postgres or API. On every query an AI agent or engineer runs against production, hoop.dev decides to pass, mask, block, or hold it for human review.

Your login details and client tools stay as they are while hoop.dev guardrails refuse bad deletes or drops before they run, and hoop.dev data masking rewrites matched sensitive values in responses in memory. Furthermore, hoop.dev holds SOC 2 Type II, is MIT licensed, and writes every query and decision to an audit trail that generates evidence for HIPAA, GDPR, and PCI DSS. See the source on GitHub.

Frequently asked questions

Why can a user still read tables after I remove their rights?

The user likely gets permissions from the built-in public role or a parent group role. Check all parent roles and strip schema usage rights from the public group.

How do I remove a role that still owns tables in the cluster?

Greenplum blocks you from dropping any role that still owns database objects or holds default privileges. Reassign all owned tables and views to an admin role, then drop the remaining privileges.

Do permission changes stop queries that are already running?

Catalog changes apply to new queries, but open sessions stay connected to the coordinator node. You must find and stop the active session process to cut off the user right away.

The query runs. The emails stay hidden.