How to Configure Zapier Postgres Read Only Access

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up zapier postgres read only access by creating a dedicated database role. You then grant that role select rights on the specific tables Zapier needs. Many teams rush this step and share a primary app user with Zapier. Broad rights let automated workflows alter production rows or read sensitive user columns. How zapier postgres read only access works in PostgreSQL PostgreSQL manages permissions through roles. You can set up group roles to manage rights when you have many users. You th

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 zapier postgres read only access by creating a dedicated database role. You then grant that role select rights on the specific tables Zapier needs. Many teams rush this step and share a primary app user with Zapier. Broad rights let automated workflows alter production rows or read sensitive user columns.

How zapier postgres read only access works in PostgreSQL

PostgreSQL manages permissions through roles. You can set up group roles to manage rights when you have many users. You then grant individual login users membership in that group role. For an external tool like Zapier, you create a dedicated login role. Next, you attach it to a read-only group role.

Every new role inherits rights from a special system role called PUBLIC. By default, PUBLIC gets the right to connect to databases and create temporary tables. It also gets the right to run functions and procedures. An object owner can revoke these default rights from PUBLIC at any time. In PostgreSQL 15 and later, regular users cannot create objects in the public schema. On older databases, admins must revoke schema creation rights from PUBLIC by hand.

Once you lock down the defaults, you grant read rights to your Zapier role. In PostgreSQL, the select privilege lets a role read from a table or view. It also works on materialized views and other table-like objects. You can grant select on a whole table or only on specific columns. Note that the select privilege also allows the role to export data out.

What native database roles solve and what they miss

A scoped role stops Zapier workflows from running write, update, or delete commands. Column-level select privileges also help you hide secret fields from a basic table query. If Zapier only needs order totals, you do not grant select on card numbers. This setup blocks accidental writes and limits which columns a basic workflow can pull.

Still, native database roles only check who logs in and which objects they touch. They do not inspect what individual queries do in flight. If a table holds emails, phone numbers, or national IDs, column grants get tricky. Admins must update column grants every time the schema changes. If a developer adds a column and grants table-wide select, Zapier pulls raw PII.

Auditor Read-Only Access + Customer Support Access to Production: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Static database credentials also create a long-term key management problem for your team. You must paste a static password into the Zapier account settings. Anyone who shares that connected app in Zapier can run any select query allowed. Native database logs also link every query to that single shared Zapier role. You cannot see which workflow or team member triggered a heavy read query.

Why schema changes break static read roles

In PostgreSQL, table grants only apply to objects that exist right now. When your team creates a new table, the Zapier role cannot read it yet. An admin must adjust default privileges for the role that runs database migrations. If multiple roles run migrations, you must update default privileges for each one.

This manual work pushes teams to grant broad schema-wide select rights to save time. Once you grant broad select rights, every new column is open to Zapier workflows. Data masking that rewrites matched fields in results is missing from native Postgres roles. Without field masking, raw emails and card numbers flow straight into third-party task logs.

Run guardrails and masking on the Postgres connection

Role grants and firewall rules are needed setup. Setup alone cannot check single SQL statements or mask sensitive rows in a response. By design, hoop.dev is an open-source proxy that sits beside Postgres, RDS, or Aurora as a sidecar for your Postgres. Clients and agent tools keep their credentials and point their connection string at the listener.

On each query, hoop.dev guardrails refuse disallowed drops, deletes, or table reads before they run. Next, hoop.dev data masking rewrites sensitive fields in returned rows that match your rules. Furthermore, hoop.dev holds SOC 2 Type II and logs every statement and verdict per resource. Check out the project on GitHub.

Frequently asked questions

Can a Postgres read-only role still run functions that change data?

Yes, if the function runs with the rights of its owner. By default, the PUBLIC role can run functions and procedures. You must revoke execute rights from PUBLIC to stop this.

How do I limit Zapier to specific columns in a Postgres table?

PostgreSQL lets you grant the select privilege on specific columns of a table. You can also create a view with only safe columns and grant select on it.

Why can my new read-only role create tables in the public schema?

Databases created before PostgreSQL 15 let the PUBLIC role create objects in the public schema. An admin must revoke create rights on the public schema from the PUBLIC role.

The query runs. The emails stay hidden.