Build1 publisher3 min readPublished
Postgres diagram tools need only CONNECT and USAGE when they read pg_catalog
Schemity's developer showed that a PostgreSQL role with only CONNECT and USAGE reads the full schema from pg_catalog while every table SELECT fails. Diagram tools built on information_schema show that same role an empty database.
The Engineer · Build desk
What happened
- The common way to give a diagram tool a login, GRANT SELECT ON ALL TABLES or the predefined pg_read_all_data role, hands over customer data along with the schema.
- The schema-only role cannot alter anything: ALTER TABLE fails with 'must be owner of table customers' and CREATE TABLE with 'permission denied for schema app'.
- A SECURITY DEFINER function, executable by PUBLIC under the default, returned the 50,000-order count to the same role that could not select a single row.
- Without table privileges the role still read check constraints as written and a column comment saying the negotiated discount is capped at 30 by finance.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- decision Picking a schema tool now includes checking which catalogue it queries, because an information_schema client under this role pushes the team back to the SELECT grant it set out to avoid.
- exposure Any account that can connect can read comments, check constraints and view text, so business rules written into the schema reach every login on the server, not only the review role.
- cost Before the role goes to a vendor tool, owners have to find SECURITY DEFINER functions that touch data and revoke EXECUTE from PUBLIC, or the no-rows boundary has a hole in it.
The role takes three statements: `CREATE ROLE schema_reader LOGIN PASSWORD 'change-me';`, `GRANT CONNECT ON DATABASE shop TO schema_reader;` and `GRANT USAGE ON SCHEMA app TO schema_reader;` [5]. According to the post, any role that can connect can read pg_catalog, and those two grants depend on nothing else [2]. psql's `\d` reads pg_catalog [15]. Connected as schema_reader, `\d app.orders` printed every column, the identity default, the primary key, the foreign key to customers and a Referenced by line for a table added after the grants [7]. The next statement, `SELECT * FROM app.orders LIMIT 1`, failed with "ERROR: permission denied for table orders" [8].
The SQL-standard views apply a filter that the catalogue does not. The PostgreSQL page for information_schema.columns says: "Only those columns are shown that the current user has access to (by way of being the owner or having some privilege)." [13] USAGE is a privilege on the schema, not on the tables in it [14]. The standard views are behaving exactly as documented. In the test, information_schema.tables, .columns and .table_constraints returned 0 rows for app, while pg_attribute returned all 12 columns and pg_constraint returned every key and check [14].
Leaving out USAGE fails more quietly. pg_class is readable by everyone, so the schema's tables still appear in the catalogue [12]. The search_path documentation says a schema "for which the user does not have USAGE permission, is silently ignored" [17]. current_schema() came back NULL in the test. A tool that filters its catalogue queries by the current schema therefore reads nothing and reports no error [12].
I'd copy this design for any review login. No table is granted anything, so there are no default privileges to keep in step [9]. A table created tomorrow shows up in the catalogue for this role at once, and its rows are refused the same way [9].
The catalogue also holds business logic. Through pg_get_viewdef the role read view definitions, including a business threshold written into one of them [19]. The post lists function bodies and row estimates among what the catalogue still shows a role with no table privileges [18].
All of these results come from one setup. It was PostgreSQL 18.3 in a throwaway container, with an app schema holding 5,000 customers, 50,000 orders, a view, a function and an enum [4]. One default decides whether the CONNECT grant matters at all. PUBLIC already has CONNECT on a new database, so that statement only changes anything where the default has been revoked, as it is on hardened servers [6]. The author builds Schemity, a desktop ERD tool, and the post comes from its blog and uses it for the examples [1]. The checks quoted here are plain SQL and psql commands that can be rerun without it [1].
What to watch
- Whether diagram and data-dictionary tools that query information_schema add a pg_catalog path, so they can work under a CONNECT-plus-USAGE role.
- Reruns of the same checks on PostgreSQL versions older than 18.3, and on servers where PUBLIC's default CONNECT or EXECUTE has been revoked.