POSTGRES: Create read only user

read_only_user.rb

# POSTGRES READ ONLY USER

database = 'evergreen'
username = 'eg_viewer'
password = '1a2b3c4d5e' # something good
schemas  = [
  'actor',
  'asset',
  'biblio',
  'config',
  # etc.
]

puts "-- POSTGRES READ ONLY USER"
puts
puts "BEGIN;"
puts
puts "CREATE USER #{username} WITH ENCRYPTED PASSWORD '#{password}';"
puts "GRANT CONNECT ON DATABASE #{database} TO #{username};"
puts

schemas.each do |schema|
  puts "GRANT USAGE ON SCHEMA #{schema} to #{username};"
  puts "GRANT SELECT ON ALL SEQUENCES IN SCHEMA #{schema} TO #{username};"
  puts "GRANT SELECT ON ALL TABLES IN SCHEMA #{schema} TO #{username};"
  puts
end

puts "COMMIT;"