PostgreSQL 12 High Availability Cookbook - Third Edition

There's more...

As we said in the introduction, every user must have a mapping if they are to access the remote data. This is rather onerous to do manually, so we can use PostgreSQL anonymous blocks to make things easier. The following SQL statement, for instance, will map all local users under the assumption that the remote system has the same users:

DO $$ 
DECLARE 
  user_name VARCHAR; 
BEGIN 
  FOR user_name IN 
      SELECT usename FROM pg_user 
  LOOP 
    EXECUTE 
      'CREATE USER MAPPING FOR ' || user_name || ' 
      SERVER primary_db 
      OPTIONS (user ' || quote_literal(user_name) || ')'; 
  END LOOP; 
END; 
$$ LANGUAGE plpgsql; 

Feel free to modify the SELECT phrase that we used to only target certain groups of users. This isn't the only way PostgreSQL anonymous blocks make ...