A PostgreSQL Role That Reads the Schema but Not the Data: What to Grant

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: Grant CONNECT on the database and USAGE on the schema, and nothing on the tables: the role can then read the whole structure from pg_catalog while every SELECT on a table fails with permission denied. The catch is that information_schema hides everything the role has no privilege on, so tools built on it show an empty schema, and the catalogue still shows view definitions, function bodies, comments and row estimates.

A PostgreSQL role can read a database's entire structure without being able to read a single row: grant CONNECT on the database and USAGE on the schema, and grant nothing on the tables. Everything a diagram, a data dictionary or a migration review needs is in pg_catalog, which PostgreSQL lets any connected role read. The rows stay behind permission denied.

That matters because the usual answer to "give the diagram tool a login" is GRANT SELECT ON ALL TABLES, or the predefined pg_read_all_data role, and both hand over the customer data along with the schema. Every statement below was run on PostgreSQL 18.3 in a throwaway container, against a shop database with an app schema holding 5,000 customers, 50,000 orders, a view, a function and an enum.

How to give a Postgres user access to the schema but not the data

Three statements, run as a superuser, or as a role with CREATEROLE that owns the database and the schema:

CREATE ROLE schema_reader LOGIN PASSWORD 'change-me';
GRANT CONNECT ON DATABASE shop TO schema_reader;
GRANT USAGE ON SCHEMA app TO schema_reader;

PUBLIC already has CONNECT on a new database, so the second statement only matters where it has been revoked, as it is on hardened servers. The third is the one that counts. Connected as schema_reader, \d app.orders in psql prints every column, the identity default, the primary key, the foreign key to customers, and the Referenced by line for a table added after the grants were made. Then:

SELECT * FROM app.orders LIMIT 1;
-- ERROR:  permission denied for table orders

Because no table is granted anything, there are no default privileges to keep in step. A table created tomorrow shows up in the catalogue for this role at once, and its rows are refused the same way.

The role cannot change the schema either. ALTER TABLE app.customers ADD COLUMN x int fails with must be owner of table customers, because schema changes need ownership, not a grant, and CREATE TABLE app.t (id int) fails with permission denied for schema app. One exception to check for: PUBLIC can execute functions by default, so a SECURITY DEFINER function in the schema runs with its owner's rights. A test function doing SELECT count(*) FROM app.orders returned 50,000 to this role. Revoke EXECUTE on such functions from PUBLIC if they touch data.

What each grant lets the role see

The same checks, run after each step:

The first row is the surprising one. Without USAGE, the catalogue still lists the schema's tables, since pg_class is readable by everyone, but the search_path documentation says a schema "for which the user does not have USAGE permission, is silently ignored". current_schema() came back NULL, so any tool that filters its catalogue queries by the current schema reads nothing and reports no error.

Why does information_schema show no tables for a read-only user?

Because the SQL-standard views filter by privilege and the catalogue does not. The information_schema.columns page says it: "Only those columns are shown that the current user has access to (by way of being the owner or having some privilege)." USAGE on a schema is a privilege on the schema, not on the tables in it, so information_schema.tables, .columns and .table_constraints all returned 0 rows for app, while pg_attribute returned all 12 columns and pg_constraint returned every key and check.

So the grant is only half the answer. The other half is which catalogue your tool reads. A client built on information_schema shows this role an empty database, and the tempting fix, a SELECT grant, is the thing you were trying not to give. psql's \d reads pg_catalog, which is why it worked above.

What a no-data role can still learn from the catalogue

The structure is not a secret from anyone who can connect, and it carries more than table and column names. As schema_reader, with no table privileges:

• Check constraints and defaults, as written: CHECK (((discount_pct >= (0)::numeric) AND (discount_pct <= (30)::numeric))).
• Column comments: "Negotiated discount, capped at 30 by finance".
• Enum labels in order: pending, paid, shipped.
• View definitions, through pg_get_viewdef, including the business threshold inside one: HAVING (sum(total) > (10000)::numeric).
• Function bodies, in pg_proc.prosrc: UPDATE app.orders SET total = total * 0.9 WHERE customer_id = p_customer.
• Table sizes and row estimates: reltuples read 50,000 for orders and pg_total_relation_size read 4,096 kB.

What it did not get was anything from the rows. pg_stats is limited to "rows of pg_statistic that correspond to tables the user has permission to read", and it returned 0 rows for app. With pg_read_all_data granted, the same view returned statistics whose most_common_vals hold real values from the table, which is one more reason that role is the wrong one for a schema tool. If a view or function body embeds something you would not show this role, such as a customer id or a secret, the fix is to move it out of the definition, because no grant hides it.

Which credential should the role log in with?

A password for a role that can read production's structure is still a production credential sitting somewhere. On AWS RDS the alternative is IAM database authentication, where the password is a token that, per the RDS documentation, "has a lifetime of 15 minutes" and "is only used for authentication and doesn't affect the session after it is established". The role is created without a password and given the rds_iam role:

CREATE USER schema_reader;
GRANT rds_iam TO schema_reader;

The token comes from aws rds generate-db-auth-token --hostname <endpoint> --port 5432 --region <region> --username schema_reader, and AWS's own psql example connects with sslmode=verify-full against its global-bundle.pem certificate bundle. One caveat from the IAM overview page: once rds_iam is granted, "IAM authentication takes precedence over password authentication", so give it to a dedicated role like this one rather than to a shared login.

How Schemity reads the schema with this role

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. It builds the diagram from pg_catalog, not information_schema: tables, views and materialized views from pg_class, columns from pg_attribute, keys and checks from pg_constraint, indexes, enum labels, comments, the objects that depend on each table, and the row estimates and sizes that impact analysis uses. Every one of those queries was run as schema_reader above and returned the full app schema, so the role can reverse engineer the database into a diagram and understand your schema without a single table grant.

To connect with it, set the schema to app in the PostgreSQL connection form; Schemity reads that schema through search_path, so the USAGE grant is what makes it appear. For RDS, set Credential to Command and paste the generate-db-auth-token line: the password command runs through your login shell, its output is kept in memory for five minutes, well inside the token's 15, and it is never written to disk or the keychain. Schemity asks you to approve the exact command for that connection before its first run. Set Encryption to Verify full with the RDS bundle as the root CA.

Two things this role cannot do in Schemity, by design of the grant. Count exactly in the impact analysis drawer runs a real count on the table, so without SELECT it reports "Count failed", with the database's reason, permission denied for table orders; the finding's catalogue estimate, ~50K rows and 4.0 MB here, still shows. And Apply on a migration fails at its first statement with a permission error, must be owner of table for a change to an existing table or permission denied for schema app for a new one, which is the point of a reading role: the diagram, the findings and the migration SQL are yours to review, and the change is applied by a role that owns the tables.

Which role to give a schema tool

• Diagram, data dictionary, migration review: CONNECT and USAGE on each schema, no table grants, and a tool that reads pg_catalog. The same applies to an AI agent's database MCP server.
• Counting rows or sampling data as well: add SELECT on the specific tables, not pg_read_all_data.
• Applying migrations: a separate role that owns the tables, used from your deploy pipeline or deliberately from the migration dialog.

The broader case for pointing a diagram at production is in documenting a production database safely, and once the schema is on the canvas, grouping a legacy database by domain is the next step. The comments this role can read come from column comments written without a migration.