"P4001 The introspected database was empty" unless run with postgres user

[sfratini]

Bug description

Trying to run npx prisma instropect to generate the schema but it fails. I am using a user that was generated specifically for this database and has a grant all on the very same database.

How to reproduce

Just executing the command is enough

Expected behavior

The schema file is generated automatically

Prisma information

N/A

Environment & setup

• OS: AWS RDS
• Database: PostgreSQL 11.6
• Prisma version:
  @prisma/cli : 2.2.2
  Current platform : darwin
• Node.js version: 10.16.3
• NPM: 6.9.0

I can connect from another clients like Postico or pgAdmin with the same user/password I am using in the DB connection and I can see the tables just fine. I run a grant all on this user to the same database.

At first I thought it was that my random generated password for the user had a '@' or '?' and that was breaking the connection string, but eventually I tried it with my postgres user and it worked, so I have to assume this is a permissions issue.

I really don't like to have my root user for this so I was wondering what are the minimum permissions the user need for the introspect to work so I can grant that to the prisma user. I don't see that in the docs.

Thanks!

[pantharshit00]

@sfratini Introspection needs access to the information_schema of postgres(https://www.postgresql.org/docs/current/information-schema.html) so that it can query the table names and columns.

So any role which has read access to the postgres information schema will work with introspection. Can you please make sure that the role you are using can access the postgres information schema?

[sfratini]

Thank you! Eventually I found out it was an issue on my side. It seems I forgot to grant on the tables and only did it on the database itself. It confused me that I was able to actually see the tables on Postico but then I realized I was not able to query them.

Eventually this is the command I run in case someone finds this issue:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public to jerry;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public to jerry;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public to jerry;