Hi, I'm trying to setup a project using Prisma, but after the setup, all works well, I can see the data in prisma studio, but my info didn't appear on the dashboard and Hasura.
Hasura throws an error with "message": "permission denied for table TABLENAME", "status_code": "42501" when I open the browse rows tab and didn't show any info. Also, in modify tab all items appear empty.
Thanks in advance and sorry for my english.

@Nuno Pato, maybe you can give some hints here when you have a moment?

.

Hi Franco, what is the project's subdomain?

https://pvnvinvynliwiitdenpp.nhost.run

@Franco can you double check now? I am not sure how you created the tables in the public schema but their owner was the postgres user. I've changed it to be the nhost_hasura user which is the user responsible for the migrations when you use the console to generate them.

@Nuno Pato Thanks a lot! Now is working.

This is my prisma schema: https://pastebin.com/TUbXe9Tj
I just made a npx prisma db push to the connection string provided in /settings/database

Ah that makes sense. If you manage the sql schema using either nhost's or hasura's dashboards, the ownership for all objects created are correct. If you do use the connection string with the postgres user and prisma, you can fix the issue by either updating the migrations to set the correct owner to 'nhost_hasura' or connect to the dB afterwards and fix it manually

Thanks a lot! I will fix my schema! Thanks again

@Nuno Pato how do you change the user?

alter table-name owner to nhost_hasura;

Hello, I am having a similar issue trying to generate my database with Prisma.

Using the DB connection infos located in Settings > Database > Connection String and the CLI command prisma db push, I am able to successfully generate tables and access them from a third party Database GUI.

However, the Tables remain invisible from the Database section of Nhost Dashboard and Hasura.

The posgres connection string provided on Nhost starts with postgres://postgres:[YOUR-PASSWORD] - so the table's owner ends up being posgres instead of nhost_hasura, which seems to be causing the issue.

I've tried running the following SQL query to change the Tables owner:
REASSIGN OWNED BY postgres TO nhost_hasura;

And I got returned the following error:
ERROR: cannot reassign ownership of objects owned by role postgres because they are required by the database system

What would be the best approach to fix this issue?

Anyone?

Hey @maoosi I ran into the same issue when attempting my import.

It's because there are necessary system tables owned by postgres, and the REASSIGN OWNED query doesn't discriminate.

I was able to use this to generate the necessary queries (it only pulls tables from the public schema):
select 'ALTER TABLE ' || t.tablename || ' OWNER TO nhost_hasura;' from pg_tables t where t.tableowner = 'public' and t.schemaname='public'

@Nuno Pato Maybe you can give some hints here? Sounds like postgres permission issues. We should probably write proper documentation around so it's clear for everyone how these things working.

I am able to make the Tables appear by running the following:

ALTER TABLE “[TableName]” OWNER TO "nhost_hasura";

That said, it is quite cumbersome to run this against all individual Tables, after each new Migration.

Is there a SQL statement to do the same to all Tables at once? Or maybe a way to use a DB Connection String just for migrations - already configured to use nhost_hasura?

@maoosi that postgres statement tries to change the ownership off ALL objects owned by a root user (postgres) to nhost_hasura. that is not possible, as the error indicates. what @c4 is suggesting should work.

Alternatively, you could go one by one and change the ownership for tables created using prisma/connection string… with alter table-name owner to nhost_hasura

Thanks @c4 - I have tried running the suggested SQL statement but it doesn't seem to be doing anything on my end.

The only thing that seems to work is the following:
ALTER TABLE “[TableName]” OWNER TO "nhost_hasura";

Additionally, I might add that running the above only make the Table appear inside the Database section of Nhost dashboard - but not inside Hasura and the GraphQL section of Nhost dashboard.

"Additionally, I might add that running the above only make the Table appear inside the Database section of Nhost dashboard - but not inside Hasura and the GraphQL section of Nhost dashboard."

it makes sense, you are not tracking those new tables yet… from within the hasura console, you should be able to track those new tables

Thanks @Nuno Pato - working now. For others who might encounter the same issue, I had to slightly update the SQL statement to:

DO $$
DECLARE
    table_rec record;
BEGIN
    FOR table_rec IN (
        SELECT tablename, schemaname
        FROM pg_tables
        WHERE schemaname = 'public' AND tableowner = 'postgres'
    ) LOOP
        EXECUTE 'ALTER TABLE "' || table_rec.tablename || '" OWNER TO nhost_hasura';
    END LOOP;
END $$;

Then connect to Hasura Console > Data > Public > Track Tables.

Maybe to make the above easier, we could imagine a new section in Nhost Dashboard > Database - to view all untracked Public Tables. Plus a CTA to "Track" that would automatically change the Owner to nhost_hasura + Track the table in Hasura (like if it would have been created via the Dashboard)?