postgres create limited user that can select tables and columns of those tables

I have an app that automates the making of a PostgreSQL database, and also automates the creation of a database user. This database user is given limited access for security purposes, as it will be handed off to a client to use.
The version I am using is Postgres 9.6

CREATE USER %USERSETUP% WITH
    LOGIN PASSWORD '%USERSETUPPASS%'
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    INHERIT
    NOREPLICATION
    CONNECTION LIMIT -1;

ALTER DEFAULT PRIVILEGES IN SCHEMA %SCHEMA% GRANT INSERT, SELECT, UPDATE, DELETE, REFERENCES ON TABLES TO %USERSETUP%;

Now, my test also goes into the created database, and verifies that it was created correctly, using Query Scripts to grab the Tables names, and then grab the column names inside each table (As a bonus, also grab the column data type)

Because of these permissions, user cannot use information_schema.
Also, I am only requesting query scripts with SYSTEM CATALOG please

The script I have for the table names works:

SELECT tablename FROM pg_catalog.pg_tables where schemaname = '{schema}'

And this returns the table names as a list:


business, location, person, customer, etc...

However, when I run the following two scripts to get column name, I get a return of 0, as if this user was trying to Query information_schema. I'm guessing it's because of the privileges:

SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ 'schemaaryzdhyqoi'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;

also

```
SELECT c.oid,
n.nspname,
c.relname, t.*
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN LATERAL (
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(
SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid
AND d.adnum = a.a

You can set column level privileges to very specifically (and narrowly) allow otherwise unprivileged users to access data. In your case, it is two catalogs: pg_attribute and pg_type.

I have a user called alice who is similarly restricted as your user:

alice@test=> > SELECT * FROM pg_class LIMIT 1;
ERROR:  permission denied for relation pg_class

Issue the following GRANT statements:

GRANT SELECT (oid, typname) ON pg_type TO alice;
GRANT SELECT (attrelid, attname, atttypid, attnum) ON TABLE pg_attribute TO alice;

After these, alice can do the following, for example:

SELECT attname, typname 
  FROM pg_attribute JOIN pg_type t ON t.oid = atttypid
 WHERE attrelid = 't2'::regclass AND attnum > 0;

 attname │ typname 
─────────┼─────────
 key     │ jsonb
 value   │ jsonb

You can trivially extend the privileges to match your needs.

StackExchange Database Administrators Q#195727, answer score: 9