PostgreSQL RDS避免在使用dblink_connect()时对连接密码进行硬编码 [英] PostgreSQL RDS avoid hard coding the connection password when using dblink_connect()

问题描述

我有一个PostgreSQL的AWS RDS实例，在该实例中，我需要在使用dblink_connect(text)和dblink_exec(text)角色(我创建的)登录时使用dblink_connect(text)和dblink_exec(text)在函数内执行SQL语句.

I have an AWS RDS instance of PostgreSQL in which I need to execute an SQL statement within a function using dblink_connect(text) and dblink_exec(text) while logged in with the postgres role (that I created).

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;

dblink_exec()产生以下错误消息:

[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')"

我找到了

I found an answer that suggested using dblink_connect_u(text). When I tried this I got the following error messages:

[42501] ERROR: permission denied for function dblink_connect_u
Where: SQL statement "SELECT dblink_connect_u('dbname=application user=postgres')"

在AWS上，如何授予创建RDS实例的用户执行功能dblink_connect_u()的权限?我尝试了以下方法，但均未成功:

On AWS how can I give the user that created the RDS instance permission to execute function dblink_connect_u()? I tried the following without success:

GRANT EXECUTE ON FUNCTION dblink_connect_u(text) TO postgres;
GRANT EXECUTE ON FUNCTION dblink_connect_u(text, text) TO postgres;

似乎我的postgres用户需要我显然在AWS上无法拥有的superuser权限.

It seems my postgres user needs superuser permissions to which I apparently cannot have on AWS.

我还玩过使用%APPDATA%\postgresql\pgpass.conf(在Windows上)提供dblink_connect(text)的密码，但是dblink_connect(text)显然忽略了此文件.

I also played with using %APPDATA%\postgresql\pgpass.conf (on Windows) to provide the password for dblink_connect(text) but dblink_connect(text) apparently ignores this file.

我能够使用硬编码的密码字符串来调用dblink_connect(text)，如下所示:

I was able to use a hard coded the password string to call dblink_connect(text) as follows:

PERFORM "pascal"."dblink_connect_u"('dbname=pascal user=postgres password=secret');

...由于密码的硬编码，最终，这是不可接受的解决方案.

...ultimately, this is not an acceptable solution due to the hard coding of the password.

有人对如何使RDS PostgreSQL使用密码文件或允许我GRANT EXECUTE ON FUNCTION dblink_connect_u(text)提出任何建议吗?或者还有我没有遇到的另一种选择吗?

Does anyone have a suggestion for how to get RDS PostgreSQL to either use the password file or allow me to GRANT EXECUTE ON FUNCTION dblink_connect_u(text) or is there another alternative that I haven't come across?

更新

链接到PostgreSQL文档，以尝试设置外部数据包装器/服务器，在其中存储正在执行dblink_connect(text)

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 dblink_connect(text)

创建服务器

创建外部数据包装器

创建用户映射

结论

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&d');

...其他源文件...

... different source file ...

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;

推荐答案

您可以创建用户映射:

1. create server application_srv foreign data wrapper dblink_fdw OPTIONS (...;
2. create user mapping FOR app_user SERVER application_srv OPTIONS (user 'user_to_connect', password 'password goes here');
3. 在dblink连接名称中使用application_srv:

1. create server application_srv foreign data wrapper dblink_fdw OPTIONS (...;
2. create user mapping FOR app_user SERVER application_srv OPTIONS (user 'user_to_connect', password 'password goes here');
3. use application_srv in dblink connection name:

.

t# select * from dblink('application_srv','select max(t) from t') as t(m timestamp(0));
          m
---------------------
 2017-06-13 11:41:05
(1 row)

现在密码将在pg_user_mappings中以纯文本显示(对于RDS中的任何人都不能选择pg_user_mapping)，但是实际的通行证将仅显示给rds_superuser

now the password will be displayed plain text in pg_user_mappings (pg_user_mapping should be not selectable for anyone in RDS), but the actual pass will be displayed only to rds_superuser

这篇关于PostgreSQL RDS避免在使用dblink_connect()时对连接密码进行硬编码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！