How to Configure Greenplum Read Only User

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a greenplum read only user by granting login rights and read privileges. Next, you apply default privileges so the role can read new tables. Data teams build these restricted roles for analysts, support staff, and reporting tools. Without strict role limits, a user might run an update command by mistake. Overly broad permissions can corrupt warehouse tables or drop critical schema objects. How to set up a greenplum read only user Greenplum uses a role model based on PostgreSQL to m

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 a greenplum read only user by granting login rights and read privileges. Next, you apply default privileges so the role can read new tables. Data teams build these restricted roles for analysts, support staff, and reporting tools. Without strict role limits, a user might run an update command by mistake. Overly broad permissions can corrupt warehouse tables or drop critical schema objects.

How to set up a greenplum read only user

Greenplum uses a role model based on PostgreSQL to manage database permissions. Admins first create a base role that can log in to the cluster. A new role cannot read any data tables right after you create it. You must grant usage rights on each target schema in the database. After that, you grant select rights on all existing tables and views. This two-step process lets the role find the schema and read the rows.

Most teams group these permissions into a shared group role for easier upkeep. You grant schema usage and table select rights to the group role first. Then you assign individual analyst accounts to that group role as team members. When a person leaves the team, you remove their account from the group. You do not need to change permissions on every single warehouse table.

You also have to check public schema rules when you set up roles. By default, Greenplum lets any database user create objects in the public schema. Admins must revoke create rights on the public schema from the public group. This step stops a read role from building rogue tables in shared spaces. You should also limit temporary table rights if analysts do not need them.

Managing default permissions for future tables

Granting select rights only covers the tables that exist at that exact moment. Data pipelines create new tables and partitions in a data warehouse every day. A basic read role will fail to query any table created tomorrow. Admins fix this by changing the default privileges for the target schema. Default privileges tell Greenplum to grant select rights on all future tables.

There is a catch with how default privileges work in Greenplum. The rule only applies to the specific role that creates the new table. If a pipeline role builds a table, the default rule must match it. If an admin creates a table by hand, the analyst cannot read it. You must set default rules for every role that runs build jobs. Missing even one writer role leads to broken dashboards and support tickets.

Read-Only Root Filesystem + User Provisioning (SCIM): architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native database roles leave open

A native read role stops users from changing or deleting warehouse data. It works well to block drop commands, table updates, and row inserts. Still, database roles only check who logs in and which tables they touch. They cannot control what a query returns once the user has select rights. If a table holds sensitive columns, the read role returns every single field.

Raw warehouse tables often store emails, phone numbers, card numbers, and national IDs. A standard read role exposes all of those fields to every reporting user. Teams often build extra views to hide sensitive columns from basic read roles. Maintaining hundreds of custom views across big schemas takes a lot of time. Static passwords for these read roles also end up stored on local laptops. Also, native role setups do not tie queries to central identity provider groups.

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

Why can my read role still create tables in the public schema?

Greenplum grants public schema creation rights to all users by default. You must revoke create rights on the public schema from the default group.

Why does a greenplum read only user fail to query new partitions?

Select grants only apply to existing tables and partitions at creation time. You must set default privileges for the role that builds the new partitions.

How do I stop a read role from seeing sensitive columns?

Native table grants apply to the whole table unless you set column rules. You must either manage column grants, build custom views, or apply runtime masking.

The query runs. The emails stay hidden.