Postgresql readonly role and user

Question

I couldn't find an answer to this question: why does selecting from the table fail after the privileges were granted?

-- create new role
CREATE ROLE readonly;

-- grant access to all existing tables
GRANT CONNECT ON DATABASE shop TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly;

-- grant access to all table which will be created in the future
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO readonly;

-- create user and grant role to this user
CREATE USER b_readonly WITH PASSWORD 'reAdOnLy123';
GRANT readonly TO b_readonly;

My error message from db is following:

ERROR: permission denied for relation customer_search_query SQL state: 42501

Is there some new trick in Postgresql 9.6.5?

Answer 1

If pg version < 14 try as:

postgres=# CREATE ROLE readaccess;
postgres=# CREATE USER read_user WITH PASSWORD 'read_password';
postgres=# GRANT readaccess TO read_user;
    
--- INPORTANT (select needed db)---
postgres=# \с your_db;
your_db=# GRANT CONNECT ON DATABASE your_db TO readaccess;
your_db=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;

if pg version >= 14

 GRANT pg_read_all_data TO readaccess;

Answer 2

It is likely that the table you're querying from, customer_search_query is not in the public schema. Try running this command.

GRANT SELECT ON customer_search_query TO readonly;