This is the SQL script to create read-only access user account on PostgreSQL database

read-only-access.sql

-- References
-- https://serverfault.com/questions/60508/grant-select-to-all-tables-in-postgresql
-- https://blog.redash.io/postgres-readonly/

-- Create a read-only role

CREATE ROLE myapp_readonly_role_name;
GRANT CONNECT ON DATABASE db_name TO myapp_readonly_role_name;
GRANT USAGE ON SCHEMA schema_name TO myapp_readonly_role_name;

-- Grant permissions to the new role for specified table

GRANT SELECT ON TABLE "db_name"."table_name" TO myapp_readonly_role_name;
GRANT SELECT ON TABLE "db_name"."table_name2" TO myapp_readonly_role_name;
GRANT SELECT (field1, field2) ON TABLE db_name.table_name TO myapp_readonly_role_name;

-- Grant permissions to the new role for specificed tables

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO user_name;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA schema_name TO user_name;

-- Create user for Redash and assign it the read only role

CREATE USER user_name WITH PASSWORD 'user_password';
GRANT myapp_readonly_role_name TO user_name;