Simple SELECT query returns no result in Postgres database, depending on user

Question

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?

Are [Row Security Policies](https://www.postgresql.org/docs/current/ddl-rowsecurity.html) in use? A.k.a."Row-Level Security". — Erwin Brandstetter, May 19 '20 at 16:44
Actually, I think they are being used. How can I enable them for my `new_user`? — Douglas Toledo, May 19 '20 at 16:50
What steps are you following to connect as new_user. Can you share the output of - select 'Database : ' ||current_database()||', '||'User : '|| user db_details; — J Sidhu, May 19 '20 at 17:07
I am trying to connect to my databse through a web service called Metabase. I am connecting by a connection string, ex: `postgres://user:password@db_url:5432/my_db`. The query you asked me to run outputed the name of `my_database` and `new_user`. — Douglas Toledo, May 19 '20 at 17:20

Erwin Brandstetter · Accepted Answer · 2020-05-19T17:21:12.793

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);

Thank you! Worked like a charm. – Douglas Toledo May 19 '20 at 17:29 — Douglas Toledo, May 19 '20 at 17:29

Simple SELECT query returns no result in Postgres database, depending on user

1 Answers1