Create a user with read-only access to a PostgreSQL schema

ro_user.sql

-- Create the user. You only have to run this once.
CREATE ROLE joebloggs WITH LOGIN PASSWORD '...';

-- Once the user is created, give access to the tables.
-- Note, if a table is dropped and recreated, you will
-- have to re-run the GRANT SELECT command.
GRANT USAGE ON SCHEMA my_schema TO joebloggs;
GRANT SELECT ON ALL TABLES IN SCHEMA my_schema TO joebloggs;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA my_schema TO joebloggs;

-- Additionally, there may be functions in public (e.g., 
-- if PostGIS is installed there) that you want the user
-- to be able to run.
GRANT USAGE ON SCHEMA public TO joebloggs;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO joebloggs;



-- =====================================================
--
-- OTHER STUFF
--

-- To revoke permissions, you basically use REVOKE in 
-- place of GRANT. For example:
REVOKE SELECT ON ALL TABLES IN SCHEMA my_schema FROM joebloggs;
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA my_schema FROM joebloggs;
REVOKE USAGE ON SCHEMA my_schema FROM joebloggs;

-- To be specific about which tables, use the table name
-- in place of ALL TABLES IN SCHEMA. For example:
GRANT SELECT ON my_schema.my_table TO jobloggs;