1. Create the role

    A login role with a password of its own. Do not reuse an application role — the point is that this one is separate and weaker.

  2. Let it connect and see the schema

    CONNECT on the database and USAGE on the schema. Without USAGE the role can log in and then see nothing at all, which looks like an empty database rather than a permissions problem.

  3. Grant SELECT, including on tables that do not exist yet

    ALTER DEFAULT PRIVILEGES is the step people skip. Without it the role loses sight of every table created after today, and the scan quietly covers less than you think.

  4. Make read-only the default for the role

    Setting default_transaction_read_only on the role means every session it opens is read-only from the first statement, without the client having to ask for it.

  5. Check it from the outside

    Connect as the new role and try to create a table. It should be refused. A permission you have not tried is a permission you are guessing about.

The SQL

psql — run as an administrator 
CREATE ROLE ledar_reader LOGIN PASSWORD 'choose-something-long';

GRANT CONNECT ON DATABASE your_database TO ledar_reader;
GRANT USAGE   ON SCHEMA   public        TO ledar_reader;
GRANT SELECT  ON ALL TABLES IN SCHEMA public TO ledar_reader;

-- tables created after today, which is the step people miss
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO ledar_reader;

-- every session this role opens starts read-only
ALTER ROLE ledar_reader SET default_transaction_read_only = on;

Check it before you trust it

psql — as the new role 
CREATE TABLE should_fail (id int);
-- ERROR:  cannot execute CREATE TABLE in a read-only transaction

LEDAR runs its own version of this check and prints the result before it reads anything, so you will see it again in the app. Run it yourself anyway once: the app telling you the role is read-only and the database telling you are two different pieces of evidence, and only one of them survives a bug in the app.

Why the timeouts matter too

A read-only role cannot corrupt anything, but it can still hurt a busy system: a long-running SELECT holds a lock that a migration then waits behind. LEDAR sets statement, lock and idle timeouts on its own connection for that reason.

If you want the same protection for every tool rather than just this one, set them on the role.

optional — belt and braces 
ALTER ROLE ledar_reader SET statement_timeout = '30s';
ALTER ROLE ledar_reader SET lock_timeout = '3s';
ALTER ROLE ledar_reader SET idle_in_transaction_session_timeout = '15s';