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
andpg_type
.I have a user called
alice
who is similarly restricted as your user:Issue the following
GRANT
statements:After these,
alice
can do the following, for example:You can trivially extend the privileges to match your needs.