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

permissionspostgresqlpostgresql-9.6scriptingusers

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?

Best Answer

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.