How an user can access permissions on information_schema.columns of postgresql

Question

I created one user like below.

create role testuser1 with password 'testuser1' login; 
 alter role testuser1 with createrole createdb;

Now I login into postgres using the above user like below

psql -U testuser1 -d template1 

I got template1 command prompt. Like below

template1# 

After this, I create one sample database like below

template# create database testdb; I was succesfully created db. Now, I exit from postgres. And again I tried to login with sample credentials, but with the database testdb and tried to import schema file(let it be test.sql) using below command

psql -h localhost -p 5432 -U testuser1 -d testdb -f "/mnt/hgfs/shared_ubuntu/sample_test_schema/pg/test.sql"

It's imported successfully. After this in testdb , the below is the tables.

 Schema |    Name    | Type  |  Owner   
--------+------------+-------+----------
 public | pretabinto | table | postgres
 public | serial     | table | postgres
 public | temp       | table | postgres

Now, I tried to get tables and it's columns info using the below query. But, I am getting 0 list.

SELECT table_name,column_name,data_type 
FROM information_schema.columns 
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

How should I get columns info using the above user (like newly created user).

Answer 1

Users can only see data in the COLUMNS table of INFORMATION_SCHEMA for tables on which they have read permissions. So the thing you need to do is:

GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO testuser2;

Also, I suggest NOT using the template1 database for "scratch space". That database is used as a template for creating other databases, which means you'll have lots of garbage in each database you create hereafter. I suggest using the "postgres" database we provide as a test database instead.