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

Question

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.attnum
 AND a.atthasdef
 ),
 a.attnotnull, a.attnum,
 (
 SELECT c.collname
 FROM
 pg_catalog.pg_collation c,
 pg_catalog.pg_type t
 WHERE c.oid = a.attcollation
 AND t.oid = a.atttypid
 AND a.attcollation <> t.typcollation
 ) AS attcollation
 FROM pg_catalog.pg_attribute a
 WHERE a.attrelid = c.oid
 AND a.attnum > 0
 AND NOT a.attisdropped
) AS t
WHERE n.nspname ~ '^(schemaaryzdhyqoi)$' -- YOUR SCHEMA HERE
AND pg_catalog.pg_table_is_visible(c.oid);

Question is: How do I add the permission for the limited user to also be able to access just the name (and maybe datatype) of the columns inside each table in the schema?

• Evan Carroll: try adding GRANT USAGE ON SCHEMA %SCHEMA% TO %USERSETUP%;
• Evan Carroll: It's a whole different line, GRANTING on a schema not a TABLE
• as.beaulieu: I have added this line of code, and unfortunately I have no change; still returning 0 on these scripts

Answer 1

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

• bertday: In the two GRANT SELECT statements: why does one says ON <table> and the second says ON TABLE <table>? Are these equivalent?
• András Váczi: @bertday they are equivalent, but I've edited it in order to be less confusing. Thanks for pointing this out!