Error: Database does not have any tables ... but it does

[kohenkatz]

I am trying to connect to a database running on another machine on the network. When I connect with pgAdmin it works perfectly, so I know that it should work.

I try to run pgweb with the following command:

 pgweb_windows_amd64.exe /url:"postgres://vagrant:vagrant@192.168.248.33/mydbname"

Here is the error that it returns:

Connecting to server...
Checking tables...
Error: Database does not have any tables

I turned on connection logging on the server, and here is what I got:

2014-10-29 01:11:30 GMT 54503ec2.7ee9 vagrant mydbname LOG:  statement: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_schema,table_name;
2014-10-29 01:11:30 GMT 54503ec2.7ee9 vagrant mydbname LOG:  could not receive data from client: Connection reset by peer

When I run that query (SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_schema,table_name;) in pgAdmin, it returns the list of tables, as expected.

What is causing this?

Is there any other information that can be used to troubleshoot?

[sosedoff]

Hm, thats strange. Are you running pgAdmin locally or inside vagrant ?

[kohenkatz]

I'm running pgAdmin on my Windows Host machine, and connecting to the IP address of the Vagrant machine, exactly the same as I'm trying to do with pgweb.

[kohenkatz]

The Linux version running on the VM does exactly the same thing:

./pgweb_linux_386 --url="postgres://vagrant:vagrant@192.168.248.33/mydbname"
# both versions do the same thing
./pgweb_linux_amd64 --url="postgres://vagrant:vagrant@192.168.248.33/mydbname"

shows this:

Connecting to server...
Checking tables...
Error: Database does not have any tables

[sosedoff]

Ok, seems like something is broken. Im going to test with vagrant to replicate the issue.

[kohenkatz]

The Vagrant box is running Ubuntu 14.04 LTS and Postgres 9.3 installed from the package manager.

[sosedoff]

Just tested on vagrant box. Works fine. Can you run the following on your host machine:

psql -h 192.168.248.33 -U vagrant -W mydbname

And when connection is established, this:

\dt

Output should include all available tables in the database.

[kohenkatz]

Running psql -h 192.168.248.33 -U vagrant -W mydbname and \dt works perfectly - it lists all of the tables.

Here is the query shown in the query log when I run \dt:

2014-10-29 02:58:28 GMT 545057d1.287b vagrant mydbname LOG:  statement: SELECT n.nspname as "Schema",
      c.relname as "Name",
      CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
      pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
    FROM pg_catalog.pg_class c
         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind IN ('r','')
          AND n.nspname <> 'pg_catalog'
          AND n.nspname <> 'information_schema'
          AND n.nspname !~ '^pg_toast'
      AND pg_catalog.pg_table_is_visible(c.oid)
    ORDER BY 1,2;

[sosedoff]

Im not sure what could cause this. Can you try to run pgweb with url from this gist: https://gist.github.com/sosedoff/20f1799215d35e2bfc32
Its a dummy heroku database with 1 table and no rows. If it does not work i'll probably have to add a bunch of debug statements to see where command/connection fails.

[kohenkatz]

I am able to run pgweb with that URL, from both my Windows machine and my Vagrant VM.

[kohenkatz]

I can post my postgresql config files somewhere if that helps.

[sosedoff]

Yes please, i would like to see your postgresql.conf and pg_hba.conf configs. Would prefer gists thought.

[Jellyfrog]

Same here.

Its because of:
PG_TABLES = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_schema,table_name;"

pgweb/client.go

Line 14 in bf2df4e

PG_TABLES = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_schema,table_name;"

table_schema = 'public' we don't have a schema named public

Also; Why do we need tables to start the UI? Maybe you want to run CREATE TABLE.. in the UI?

[kohenkatz]

@Jellyfrog The default schema is named public - and my database is using the default. As I mentioned previously, if I run that exact query in pgAdmin on Windows or in psql on Linux, I get the table list as expected.

[kohenkatz]

Here are my config files: https://gist.github.com/kohenkatz/d53eeafa27e33fb623ed

Other than adding two access lines in pg_hba.conf and two logging lines in postgresql.conf, the two files are completely Ubuntu default settings, AFAIK.

[kohenkatz]

I tried turning off SSL on the server so that I could use WireShark on the connection to see if that shows anything. (This requires adding ?sslmode=disable to the end of the connection URL.)

I'll see if that turns up anything interesting.

[sosedoff]

@kohenkatz any luck ?

[fijosh]

I am able to reproduce the problem when I use an user that doesn't have permissions to see the 'public' schema.
When I user 'postgres' user which has the permission, the problem does not occur.
@kohenkatz do you really see some table(s) when you run

psql -h 192.168.248.33 -U vagrant -W mydbname -c " SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_schema,table_name;"

Either way, @sosedoff I think it would be better to rewrite the query for schemas and tables, in order to list tables from all available schemas, not only from public (which is not always accessible, as it looks to be the issue here).
For example, apart from the 'public' schema with only table 'dual', I have also 2 other schemas with tens of tables in the DB, but in the WebUI I can only see the sad and empty table 'dual'.

If you already have this on the roadmap or maybe I am doing something wrong, then I apologize, I just stumbled upon this here and had very little time to look around so far ;)

[sosedoff]

@fijosh This makes sense, although its not a quick fix and requires a few evenings of work.

[kohenkatz]

@fijosh Actually, you are right that running that exact command gives no output. The query only returns results when I run it as the postgres user or as the owner of the database (the application-specific user).

However, it doesn't seem that granting access to the public schema actually fixes this. I tried all of the following, with no success:

• GRANT ALL ON SCHEMA public TO vagrant;
• GRANT USAGE ON SCHEMA information_schema TO vagrant;
• GRANT USAGE ON SCHEMA pg_catalog TO vagrant;

What I found that I had to do in the end is explicitly set GRANT ALL ON TABLE ______ TO vagrant; where ______ means doing the same thing for every table in the database. I would like to think that there's an easier way to do that, but that is all I could find.

[cbandy]

I had to do in the end is explicitly set GRANT ALL ON TABLE ______ TO vagrant; where ______ means doing the same thing for every table in the database.

9.x has ALL TABLES IN SCHEMA schema_name

[fijosh]

@sosedoff agreed on that, definitely not a small fix, but it will bring so much value to this tool :)
Anyways I would discard this issue as it seems it was simply an issue with permissions for given user

[sosedoff]

Closing this since i was not able to reproduce the issue on most common setups (local and remote).