Create Read only User in PostgreSQL ( RDS and On-Prem)

-- Create a group

CREATE ROLE readaccess;

-- Grant access to existing tables

GRANT USAGE ON SCHEMA public TO readaccess;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;

-- Grant access to future tables

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

-- Create a final user with password

CREATE USER rahul WITH PASSWORD 'password';

GRANT readaccess TO rahul;

CREATE USER rahul_saha WITH PASSWORD 'password';

GRANT readaccess TO rahul_saha;

It is allowed by default for every user to create tables in public schema. If you want to mitigate this, do the following:

REVOKE ALL ON SCHEMA public FROM public

GRANT ALL ON SCHEMA public TO writeuser