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

> 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.

Source: https://schemity.com/blog/postgres-role-read-schema-not-data/

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:

```sql
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:

```sql
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:

| Role has | Tables it can describe in `pg_catalog` | Rows in `information_schema.columns` for `app` | `SELECT` on `app.orders` |
| --- | --- | --- | --- |
| `CONNECT` only | All of them, but `SET search_path TO app` resolves to nothing | 0 | `permission denied for schema app` |
| `CONNECT` and `USAGE` on `app` | All of them, and after `SET search_path TO app`, `current_schema()` is `app` | 0 | `permission denied for table orders` |
| `pg_read_all_data` | All of them | 12 | 50,000 rows |

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](https://www.postgresql.org/docs/current/runtime-config-client.html) 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](https://www.postgresql.org/docs/current/infoschema-columns.html) 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`](https://www.postgresql.org/docs/current/view-pg-stats.html) 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`](https://www.postgresql.org/docs/current/predefined-roles.html) 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](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.html), "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:

```sql
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](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.Connecting.AWSCLI.PostgreSQL.html) 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.

| Credential | Where it lives | How long it works |
| --- | --- | --- |
| Password | The client's store, or a config file | Until someone rotates it |
| RDS IAM token | Generated on demand from your AWS session | 15 minutes to connect |
| Vault or password manager output | Fetched on demand | Whatever the secret store allows |

## 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](https://schemity.com/doc/reverse-engineer-database/) into a diagram and understand your schema without a single table grant.

![Schemity's canvas for the app schema read as schema_reader: customers with id, discount_pct NUMERIC(5,2) default 0 and email TEXT marked U, footer field: 3, u: 1, cc: 1; orders with id, customer_id, note TEXT NULL, status ORDER_STATUS with default pending, and total NUMERIC(12,2); refunds with id and order_id; foreign key lines from orders to customers and from refunds to orders; and the big_spenders view with customer_id and spent](https://schemity.com/images/blog/postgres-role-read-schema-not-data/canvas-schema-reader.webp)

To connect with it, set the schema to `app` in the [PostgreSQL connection](https://schemity.com/doc/connect-postgresql/) 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](https://schemity.com/doc/connections-overview/#can-the-password-come-from-a-command-instead-of-being-stored) 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.

![Schemity's New diagram form for a PostgreSQL connection: host shop-prod.abc123.us-east-1.rds.amazonaws.com on port 5432, username schema_reader, Credential set to Command with the command aws rds generate-db-auth-token --hostname shop-prod.abc123.us-east-1.rds.amazonaws.com --port 5432 --region us-east-1 --username schema_reader, a Test command button, database shop, schema app, and Encryption set to Verify full (recommended)](https://schemity.com/images/blog/postgres-role-read-schema-not-data/connection-password-command.webp)

Two things this role cannot do in Schemity, by design of the grant. **Count exactly** in the [impact analysis](https://schemity.com/doc/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.

![Schemity's Findings drawer for making orders.note NOT NULL on the schema_reader connection: the planned migration SET search_path TO "app", public; and ALTER TABLE "orders" ALTER COLUMN "note" SET NOT NULL, one planned change reading Column orders.note altered, one lint finding reading orders.customer_id Foreign key has no index on its columns, and two impact findings reading Sets orders.note NOT NULL, fails if any of ~50K rows is NULL and Checking note for NULL holds back reads and writes to orders while it reads ~50K rows, 4.0 MB on disk](https://schemity.com/images/blog/postgres-role-read-schema-not-data/preview-changes-schema-reader.webp)

## 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](https://schemity.com/blog/database-mcp-server-sql-access-or-schema-only/).
- **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](https://schemity.com/blog/documenting-production-shouldnt-feel-dangerous/), and once the schema is on the canvas, [grouping a legacy database by domain](https://schemity.com/blog/reverse-engineer-legacy-database-group-by-domain/) is the next step. The comments this role can read come from [column comments written without a migration](https://schemity.com/blog/column-comments-without-a-migration/).

## Frequently asked questions

### How do I give a Postgres user access to the schema but not the data?

Create a login role, grant it `CONNECT` on the database and `USAGE` on the schema, and grant nothing on the tables. The role can describe every table, column, constraint, index and view through `pg_catalog`, and `SELECT * FROM app.orders` fails with `permission denied for table orders`. Tables created later are visible too, with no default privileges to maintain.

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

Because the `information_schema` views only list objects the current user owns or holds some privilege on, and `USAGE` on the schema is not a privilege on its tables. A role with no table grants saw 0 rows in `information_schema.columns` for a schema whose 12 columns it could read in `pg_attribute`. Tools that read `pg_catalog`, such as psql's `\d` and Schemity, see the full structure.

### Is pg_read_all_data a safe role for a schema or ERD tool?

No, if the point is to keep the tool away from the data. `pg_read_all_data` acts as `SELECT` on every table and `USAGE` on every schema, so the role can read every row, and `pg_stats` then shows real values from the table in `most_common_vals`. A diagram only needs the catalogue.