I am building an integration test against an app that provisions a database for a user. The User created for it is not a super user, and does not have access to schema_information.tables because when I try the following script:

SELECT table_name FROM information_schema.tables WHERE table_schema='{schema}' 

I get a return of 0, as I should since this user does not have permission.

I am trying to query the database to verify the tables and the columns created. I can get the list of table names through the system catalog with the following script:

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

And this outputs the table names the way I want it:

business, location, person, etc…

I can’t find the script with system catalog to then find the column names (And as a bonus, the data type) of each table. So far I’ve tried the following:

SELECT attname, format_type(atttypid, atttypmod) AS type FROM pg_attribute WHERE  attrelid = 'business' 

and here is the error:

ERROR:  invalid input syntax for type oid: "business" LINE 1: ...od) AS type FROM   pg_attribute WHERE  attrelid = 'business'                                                          ^``` 

Also tried:

SELECT     a.attname as "Column",     pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype" FROM     pg_catalog.pg_attribute a WHERE     a.attnum > 0     AND NOT a.attisdropped     AND a.attrelid = (         SELECT c.oid         FROM pg_catalog.pg_class c             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace         WHERE c.relname ~ '**Dont know what to put here, Schema? Database?**'             --AND pg_catalog.pg_table_is_visible(c.oid)     ); 

and this returns 0 with a schema or database. I’m not sure what to put there for c.relname. Am I also seeing 0 with this because a basic user just cannot see deeper than tables in a schema, period?