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?