"permission denied for schema public" caused by Postgres docs permissions setup #2404
Description
PostgreSQL version: 10.5
graphql-engine version: v1.0.0-beta.2
Provider: Docker for Mac, on local machine
I have followed the following docs for up Hasura from scratch, with an existing PostgreSQL database, using Docker for Mac on my local machine:
https://docs.hasura.io/1.0/graphql/manual/deployment/docker/index.html
This doc references the need to set up PostgreSQL permissions for the hasurauser user, as documented here: https://docs.hasura.io/1.0/graphql/manual/deployment/postgres-permissions.html
After following the instructions and attempting (unsuccessfully) to load untracked tables at http://localhost:8080/console/data/schema/public, my docker logs displays the following error:
"error":{"exec_status":"FatalError","hint":null,"message":"permission denied for schema public","status_code":"42501","description":null},"arguments":[]},"path":"$.args[0].args","error":"query execution failed","code":"postgres-error"}
Full context:
{"timestamp":"2019-06-19T18:19:23.113+0000","level":"info","type":"http-log","detail":{"status":400,"query_hash":"2e5e5268eb7f588e85fc722a8d6fa961b1009c2a","http_version":"HTTP/1.1","query_execution_time":0.1374081,"request_id":null,"url":"/v1/query","ip":"172.17.0.1","response_size":1897,"user":{"x-hasura-role":"admin"},"method":"POST","detail":{"error":{"internal":{"statement":"select \n COALESCE(\n json_agg(\n row_to_json(info)\n ), \n '[]' :: JSON\n ) AS tables \nFROM \n (\n select \n ist.table_schema, \n ist.table_name,\n ist.table_type,\n obj_description(\n (\n quote_ident(ist.table_schema) || '.' || quote_ident(ist.table_name)\n ):: regclass, \n 'pg_class'\n ) as comment, \n json_agg(\n row_to_json(isc) :: JSONB || jsonb_build_object(\n 'comment', \n (\n SELECT \n pg_catalog.col_description(\n c.oid, isc.ordinal_position :: int\n ) \n FROM \n pg_catalog.pg_class c \n WHERE \n c.oid = (\n SELECT \n (\n (\n quote_ident(ist.table_schema) || '.' || quote_ident(ist.table_name)\n ):: text\n ):: regclass :: oid\n ) \n AND c.relname = isc.table_name\n )\n )\n ) AS columns,\n row_to_json(isc_views) as view_info\n from \n information_schema.tables AS ist \n LEFT OUTER JOIN information_schema.columns AS isc ON isc.table_schema = ist.table_schema \n and isc.table_name = ist.table_name \n LEFT OUTER JOIN information_schema.views AS isc_views ON isc_views.table_schema = ist.table_schema\n and isc_views.table_name = ist.table_name\n where (ist.table_schema='public') \n GROUP BY \n ist.table_schema, \n ist.table_name,\n ist.table_type,\n isc_views.*\n ) AS info\n","prepared":false,"error":{"exec_status":"FatalError","hint":null,"message":"permission denied for schema public","status_code":"42501","description":null},"arguments":[]},"path":"$.args[0].args","error":"query execution failed","code":"postgres-error"},"request":"{\"type\":\"bulk\",\"args\":[{\"type\":\"run_sql\",\"args\":{\"sql\":\"select \\n COALESCE(\\n json_agg(\\n row_to_json(info)\\n ), \\n '[]' :: JSON\\n ) AS tables \\nFROM \\n (\\n select \\n ist.table_schema, \\n ist.table_name,\\n ist.table_type,\\n obj_description(\\n (\\n quote_ident(ist.table_schema) || '.' || quote_ident(ist.table_name)\\n ):: regclass, \\n 'pg_class'\\n ) as comment, \\n json_agg(\\n row_to_json(isc) :: JSONB || jsonb_build_object(\\n 'comment', \\n (\\n SELECT \\n pg_catalog.col_description(\\n c.oid, isc.ordinal_position :: int\\n ) \\n FROM \\n pg_catalog.pg_class c \\n WHERE \\n c.oid = (\\n SELECT \\n (\\n (\\n quote_ident(ist.table_schema) || '.' || quote_ident(ist.table_name)\\n ):: text\\n ):: regclass :: oid\\n ) \\n AND c.relname = isc.table_na
me\\n )\\n )\\n ) AS columns,\\n row_to_json(isc_views) as view_info\\n from \\n information_schema.tables AS ist \\n LEFT OUTER JOIN information_schema.columns AS isc ON isc.table_schema = ist.table_schema \\n and isc.table_name = ist.table_name \\n LEFT OUTER JOIN information_schema.views AS isc_views ON isc_views.table_schema = ist.table_schema\\n and isc_views.table_name = ist.table_name\\n where (ist.table_schema='public') \\n GROUP BY \\n ist.table_schema, \\n ist.table_name,\\n ist.table_type,\\n isc_views.*\\n ) AS info\\n\"}},{\"type\":\"select\",\"args\":{\"table\":{\"name\":\"hdb_table\",\"schema\":\"hdb_catalog\"},\"columns\":[\"table_schema\",\"table_name\",{\"name\":\"primary_key\",\"columns\":[\"*\"]},{\"name\":\"relationships\",\"columns\":[\"*\"]},{\"name\":\"permissions\",\"columns\":[\"*\"]},{\"name\":\"unique_constraints\",\"columns\":[\"*\"]}],\"order_by\":[{\"column\":\"table_name\",\"type\":\"asc\"}]},\"where\":{\"$or\":[{\"table_schema\":\"public\"}]}},{\"type\":\"run_sql\",\"args\":{\"sql\":\"select \\n COALESCE(\\n json_agg(\\n row_to_json(info)\\n ), \\n '[]' :: JSON\\n ) AS tables \\nFROM \\n (\\n select\\n hdb_fkc.*, \\n fk_ref_table.table_name IS NOT NULL AS is_ref_table_tracked \\n from \\n hdb_catalog.hdb_table AS ist \\n JOIN hdb_catalog.hdb_foreign_key_constraint AS hdb_fkc ON hdb_fkc.table_schema = ist.table_schema \\n and hdb_fkc.table_name = ist.table_name \\n LEFT OUTER JOIN hdb_catalog.hdb_table AS fk_ref_table ON fk_ref_table.table_schema = hdb_fkc.ref_table_table_schema \\n and fk_ref_table.table_name = hdb_fkc.ref_table\\n where (ist.table_schema='public')\\n ) as info\\n\"}},{\"type\":\"run_sql\",\"args\":{\"sql\":\"select \\n COALESCE(\\n json_agg(\\n row_to_json(info)\\n ), \\n '[]' :: JSON\\n ) AS tables \\nFROM \\n (\\n select\\n hdb_fkc.*, \\n fk_ref_table.table_name IS NOT NULL AS is_table_tracked \\n from \\n hdb_catalog.hdb_table AS ist \\n JOIN hdb_catalog.hdb_foreign_key_constraint AS hdb_fkc ON hdb_fkc.ref_table_table_schema = ist.table_schema \\n and hdb_fkc.ref_table = ist.table_name \\n LEFT OUTER JOIN hdb_catalog.hdb_table AS fk_ref_table ON fk_ref_table.table_schema = hdb_fkc.table_schema \\n and fk_ref_table.table_name = hdb_fkc.table_name\\n where (ist.table_schema='public')\\n ) as info\\n\"}}]}"}
I was able to resolve the issue by running GRANT USAGE ON SCHEMA public TO hasurauser; in addition to the SQL grants listed in the permission doc. I believe that this GRANT should be included in the permission doc. I will submit a PR to do that!