Using postgres permissions from docs does not work on RDS

[ecthiender]

via fusillicode#3122 on Discord.

Following the instructions here does not work on RDS.

Results in error:

sql> GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO hasurauser
[2019-02-28 13:03:13] [42501] ERROR: permission denied for relation sql_parts
sql> GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO hasurauser
[2019-02-28 13:03:17] [42501] ERROR: permission denied for relation pg_statistic

Probably because RDS restricts specific tables in pg_catalog and information_schema.

[ecthiender]

Granting selects explicitly to the relations used by hasura in pg_catalog and information_schema should work.

pg_catalog.pg_constraint
pg_catalog.pg_class
pg_catalog.pg_namespace
pg_catalog.pg_attribute
pg_catalog.pg_get_constraintdef
pg_catalog.pg_proc
pg_catalog.pg_available_extensions
pg_catalog.pg_statio_all_tables
pg_catalog.pg_description

information_schema.tables
information_schema.table_constraints
information_schema.key_column_usage
information_schema.columns
information_schema.views
information_schema.schemata
information_schema.routines

[fusillicode]

The following DDLs doesn't raise any errors from the db side:

GRANT SELECT ON information_schema.table_constraints, information_schema.key_column_usage, information_schema.columns, information_schema.views, information_schema.schemata, information_schema.routines TO <hasura-username>;

GRANT SELECT ON pg_catalog.pg_constraint, pg_catalog.pg_class, pg_catalog.pg_namespace, pg_catalog.pg_attribute, pg_catalog.pg_proc, pg_catalog.pg_available_extensions, pg_catalog.pg_statio_all_tables, pg_catalog.pg_description TO <hasura-username>;

Regarding the pg_catalog tables I've just found this thread but it doesn't explains much about the issue:
https://stackoverflow.com/questions/45095211/amazon-rds-postgres-give-permission-to-pg-catalog

[dvasdekis]

Same problem occurred on Google Cloud SQL, with the same solution. I think the documentation should be updated.

[denisotari]

The following DDLs doesn't raise any errors from the db side:

GRANT SELECT ON information_schema.table_constraints, information_schema.key_column_usage, information_schema.columns, information_schema.views, information_schema.schemata, information_schema.routines TO <hasura-username>;

GRANT SELECT ON pg_catalog.pg_constraint, pg_catalog.pg_class, pg_catalog.pg_namespace, pg_catalog.pg_attribute, pg_catalog.pg_proc, pg_catalog.pg_available_extensions, pg_catalog.pg_statio_all_tables, pg_catalog.pg_description TO <hasura-username>;

Regarding the pg_catalog tables I've just found this thread but it doesn't explains much about the issue:
https://stackoverflow.com/questions/45095211/amazon-rds-postgres-give-permission-to-pg-catalog

In my case GCP CloudSQL PostgreSQL 9.6.15 return error:

WARNING:  no privileges were granted for "table_constraints"
WARNING:  no privileges were granted for "key_column_usage"
WARNING:  no privileges were granted for "columns"
WARNING:  no privileges were granted for "views"
WARNING:  no privileges were granted for "schemata"
WARNING:  no privileges were granted for "routines"
WARNING:  no privileges were granted for "pg_constraint"
WARNING:  no privileges were granted for "pg_class"
WARNING:  no privileges were granted for "pg_namespace"
WARNING:  no privileges were granted for "pg_attribute"
WARNING:  no privileges were granted for "pg_proc"
WARNING:  no privileges were granted for "pg_available_extensions"
WARNING:  no privileges were granted for "pg_statio_all_tables"
WARNING:  no privileges were granted for "pg_description"
GRANT

Query returned successfully in 148 msec.

[koienergyspa]

The following DDLs doesn't raise any errors from the db side:

GRANT SELECT ON information_schema.table_constraints, information_schema.key_column_usage, information_schema.columns, information_schema.views, information_schema.schemata, information_schema.routines TO <hasura-username>;

GRANT SELECT ON pg_catalog.pg_constraint, pg_catalog.pg_class, pg_catalog.pg_namespace, pg_catalog.pg_attribute, pg_catalog.pg_proc, pg_catalog.pg_available_extensions, pg_catalog.pg_statio_all_tables, pg_catalog.pg_description TO <hasura-username>;

Regarding the pg_catalog tables I've just found this thread but it doesn't explains much about the issue:
https://stackoverflow.com/questions/45095211/amazon-rds-postgres-give-permission-to-pg-catalog

Using AWS Amazon RDS PostgreSQL 12 returns this:

postgres=> GRANT SELECT ON pg_catalog.pg_constraint, pg_catalog.pg_class, pg_catalog.pg_namespace, pg_catalog.pg_attribute, pg_catalog.pg_proc, pg_catalog.pg_available_extensions, pg_catalog.pg_statio_all_tables, pg_catalog.pg_description TO hasurauser;

WARNING:  no privileges were granted for "pg_constraint"
WARNING:  no privileges were granted for "pg_class"
WARNING:  no privileges were granted for "pg_namespace"
WARNING:  no privileges were granted for "pg_attribute"
WARNING:  no privileges were granted for "pg_proc"
WARNING:  no privileges were granted for "pg_available_extensions"
WARNING:  no privileges were granted for "pg_statio_all_tables"
WARNING:  no privileges were granted for "pg_description"
GRANT

[matthewsommer]

I have this issue as well on AWS RDS Postgres 12. When I try the proposed workaround I also get 'no priviledges were granted for...'

[seifip]

Got the same problem on GCP/Postgres 12 with Hasura Cloud. Workaround above does not work.

[caleb15]

I'm not even able to grant usage on the pg_catalog schema as root (superuser):

fifteen5_test=> GRANT USAGE ON SCHEMA pg_catalog TO test_user;
WARNING:  no privileges were granted for "pg_catalog"