👁‍🗨 PostgreSQL: Create readonly user

create-read-only-user.sql

-- create user (e.g. metabase)
create user metabase with encrypted password 'password';

-- allow user connect to db 
grant connect on database site to metabase; 

-- allow user access to schema
grant usage on schema public to metabase;

-- grant select on existing tables
grant select on all tables in schema public to metabase;

-- allow user to access all tables in schema 
alter default privileges in schema public
  grant select on tables to metabase;
  
-- limit number of connections 
-- alter role metabase with connection limit 10