title: PostgreSQL RDS avoid hard coding the connection password when using dblink_connect()
body: <p>I have an AWS RDS instance of PostgreSQL in which I need to execute an <code>SQL</code> statement within a function using <code>dblink_connect(text)</code> and <code>dblink_exec(text)</code> while logged in with the <code>postgres</code> role (that I created).</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION application.create_tenant_schemas(first integer, last integer) RETURNS void AS
  DECLARE
    tenant VARCHAR;
    sql VARCHAR;
  BEGIN
    FOR index IN first..last LOOP
      tenant := 'tenant_' || to_char(index, 'FM00000');
  
      sql := 'CREATE SCHEMA ' || quote_ident(tenant);
      RAISE NOTICE '%', sql;
  
      PERFORM dblink_connect('dbname=application user=postgres');
      PERFORM dblink_exec(sql);
      PERFORM dblink_disconnect();
  
    END LOOP;
  END;
  $BODY$
  LANGUAGE plpgsql;
  </code></pre>
  
  <p>The <code>dblink_exec()</code> is producing the following error messages:</p>
  
  <pre><code>[2F003] ERROR: password is required
  Detail: Non-superusers must provide a password in the connection string.
  Where: SQL statement "SELECT dblink_connect('dbname=application user=postgres')"
  </code></pre>
  
  <p>I found an <a href="https://stackoverflow.com/questions/7031737/non-superuser-cannot-connect-if-the-server-does-not-request-a-password-while-usi">answer</a> that suggested using <code>dblink_connect_u(text)</code>. When I tried this I got the following error messages:</p>
  
  <pre><code>[42501] ERROR: permission denied for function dblink_connect_u
  Where: SQL statement "SELECT dblink_connect_u('dbname=application user=postgres')"
  </code></pre>
  
  <p>On AWS how can I give the user that created the RDS instance permission to execute function <code>dblink_connect_u()</code>? I tried the following without success:</p>
  
  <pre><code>GRANT EXECUTE ON FUNCTION dblink_connect_u(text) TO postgres;
  GRANT EXECUTE ON FUNCTION dblink_connect_u(text, text) TO postgres;
  </code></pre>
  
  <p>It seems my <code>postgres</code> user needs <code>superuser</code> permissions to which I apparently cannot have on AWS.</p>
  
  <p>I also played with using <code>%APPDATA%\postgresql\pgpass.conf</code> (on Windows) to provide the password for <code>dblink_connect(text)</code> but <code>dblink_connect(text)</code> apparently ignores this file.</p>
  
  <p>I was able to use a hard coded the password string to call <code>dblink_connect(text)</code> as follows:</p>
  
  <pre><code>PERFORM "pascal"."dblink_connect_u"('dbname=pascal user=postgres password=secret');
  </code></pre>
  
  <p>...ultimately, this is not an acceptable solution due to the hard coding of the password.</p>
  
  <p>Does anyone have a suggestion for how to get RDS PostgreSQL to either use the password file or allow me to <code>GRANT EXECUTE ON FUNCTION dblink_connect_u(text)</code> or is there another alternative that I haven't come across?</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Links to PostgreSQL documentation for trying to set up a foreign data wrapper / server in which to store the password for the user that's executing <code>dblink_connect(text)</code></p>
  
  <p><a href="https://www.postgresql.org/docs/9.1/static/sql-createserver.html" rel="nofollow noreferrer">CREATE SERVER</a></p>
  
  <p><a href="https://www.postgresql.org/docs/9.1/static/sql-createforeigndatawrapper.html" rel="nofollow noreferrer">CREATE FOREIGN DATA WRAPPER</a></p>
  
  <p><a href="https://www.postgresql.org/docs/8.4/static/sql-createusermapping.html" rel="nofollow noreferrer">CREATE USER MAPPING</a></p>
  
  <p><strong>CONCLUSION</strong></p>
  
  <pre><code>CREATE SERVER "password_server" FOREIGN DATA WRAPPER "dblink_fdw"
  OPTIONS (dbname 'application');
  
  CREATE USER MAPPING FOR "postgres"
  SERVER "password_server"
  OPTIONS (user 'postgres', password 'pa55VV0&amp;d');
  </code></pre>
  
  <p>... different source file ...</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION application.create_tenant_schemas(first integer, last integer) RETURNS VOID AS $$
  DECLARE
    tenant VARCHAR;
    sql VARCHAR;
  BEGIN
    FOR index IN first..last LOOP
      tenant := 'tenant_' || to_char(index, 'FM00000');
  
      sql := 'CREATE SCHEMA ' || quote_ident(tenant);
      RAISE NOTICE '%', sql;
  
      PERFORM "dblink_connect"('password_server');
      PERFORM "dblink_exec"(sql);
      PERFORM "dblink_disconnect"();
    END LOOP;
  END;
  $BODY$
  LANGUAGE plpgsql;
  </code></pre>