How an user can access permissions on information_schema.columns of postgresql
postgresql
Solution
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.
Problem
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).