How do you create a read only user in postgresql

Grant usage/select to a single table

If you only grant CONNECT to a database, the user can connect but has no other privileges. You have to grant USAGE on namespaces (schemas) and SELECT on tables and views individually like so:

GRANT CONNECT ON DATABASE postgres TO user1; GRANT USAGE ON SCHEMA public TO user1; GRANT SELECT ON table01 TO user1;

Multiple tables/views

you can grant permissions on all tables/views/etc in the schema using a single command rather than having to type them one by one:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO user1;

This only affects tables that have already been created. More powerfully, you can automatically have default roles assigned to new objects in future:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO user1;

Or you can create role before creating a user, like i usually do

-- Create a group CREATE ROLE read_access_dwh; -- Grant access to existing tables GRANT USAGE ON SCHEMA dwh TO read_access_dwh; GRANT SELECT ON ALL TABLES IN SCHEMA dwh TO read_access_dwh; -- Grant access to future tables ALTER DEFAULT PRIVILEGES IN SCHEMA dwhp GRANT SELECT ON TABLES TO read_access_dwh; -- Create a final user with password CREATE USER report_dwh WITH PASSWORD 'yourpassword'; GRANT CONNECT ON DATABASE postgres TO report_dwh; GRANT read_access_dwh TO report_dwh;