I am trying to create a read all user for my database, but it seems the most simplest of queries isn't working properly.
When I try running the query select * from public.reports limit 5; it works fine when using the postgres user (db owner), returning 5 lines of results. However, it returns no results when using this new user. It throws no errors, it says nothing. It simply returns no results, as if the table was empty.
I created the new user using the following queries:
CREATE USER "new_user" WITH PASSWORD 'strong_password';
GRANT CONNECT ON DATABASE my_database TO "new_user";
GRANT USAGE ON SCHEMA public TO "new_user";
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO "new_user";
GRANT SELECT ON ALL TABLES IN SCHEMA public to "new_user";
ALTER USER "new_user" WITH LOGIN;
What am I missing?
So you have row-level security enabled? The manual:
If row-level security is enabled for a table, but no applicable policies exist, a “default deny” policy is assumed, so that no rows will be visible or updatable.
This would explain that your user cannot see any rows.
The simplest way to bypass RLS generally for the new user is with BYPASSRLS.
CREATE USER new_user WITH BYPASSRLS PASSWORD 'strong_password';
Else, you need CREATE POLICY to allow things. To allow all read and write operations on table reports for our new role:
CREATE POLICY foo ON public.reports TO new_user USING (true) WITH CHECK (true);