Uso de los contenedores de datos externos compatibles para Amazon RDS for PostgreSQL - Amazon Relational Database Service

Uso de los contenedores de datos externos compatibles para Amazon RDS for PostgreSQL

Un FDW es un tipo específico de extensión que proporciona acceso a datos externos. Por ejemplo, la extensión oracle_fdw permite a su clúster de bases de datos de RDS for PostgreSQL trabajar con bases de datos Oracle. Otro ejemplo, cuando utiliza la extensión nativa de PostgreSQL postgres_fdw, puede acceder a los datos almacenados en instancias de bases de datos de PostgreSQL externas a su instancia de base de datos de RDS for PostgreSQL.

A continuación, puede encontrar información sobre varios contenedores de datos externos de PostgreSQL compatibles.

Uso de la extensión log_fdw para acceder al registro de base de datos mediante SQL

RDS for PostgreSQL admite la extensión log_fdw, que puede utilizar para acceder al registro de su motor de base de datos por medio de una interfaz SQL. La extensión log_fdw proporciona dos funciones que facilitan la creación de tablas externas para los registros de la base de datos:

  • list_postgres_log_files: muestra los archivos del directorio de registro de la base de datos y el tamaño del archivo en bytes.

  • create_foreign_table_for_log_file(table_name text, server_name text, log_file_name text): crea una tabla externa para el archivo especificado en la base de datos actual.

Todas las funciones creadas por log_fdw pertenecen a rds_superuser. Los miembros del rol rds_superuser pueden conceder acceso a estas funciones a otros usuarios de la base de datos.

De forma predeterminada, Amazon RDS genera los archivos de registro en formato stderr (error estándar), tal y como se especifica en el parámetro log_destination. Solo hay dos opciones para este parámetro: stderr y csvlog (valores separados por comas, CSV). Si añade la opción csvlog al parámetro, Amazon RDS generará ambos registros stderr y csvlog. Esto puede afectar a la capacidad de almacenamiento de la instancia, por lo que debe tener en cuenta los demás parámetros que afectan a la gestión de registros. Para obtener más información, consulte Configuración del destino del registro .

Uno de los beneficios de generar registros csvlog es que la extensión log_fdw permite crear tablas externas con los datos perfectamente divididos en varias columnas. Para ello, la instancia debe asociarse a un grupo de parámetros de base de datos personalizado para que usted pueda cambiar la configuración de log_destination. Para obtener información acerca de cómo hacerlo, consulte Uso de parámetros en su instancia de base de datos de RDS for PostgreSQL.

En el ejemplo siguiente se presupone que el parámetro log_destination incluye cvslog.

Para utilizar la extensión log_fdw

  1. Obtenga la extensión log_fdw.

    postgres=> CREATE EXTENSION log_fdw; CREATE EXTENSION
  2. Cree el servidor de registros como contenedor de datos externo.

    postgres=> CREATE SERVER log_server FOREIGN DATA WRAPPER log_fdw; CREATE SERVER
  3. Seleccione todos los elementos de una lista de archivos de registro.

    postgres=> SELECT * FROM list_postgres_log_files() ORDER BY 1;

    A continuación, se muestra una respuesta de ejemplo.

    file_name | file_size_bytes ------------------------------+----------------- postgresql.log.2016-08-09-22.csv | 1111 postgresql.log.2016-08-09-23.csv | 1172 postgresql.log.2016-08-10-00.csv | 1744 postgresql.log.2016-08-10-01.csv | 1102 (4 rows)
  4. Crear una tabla con una sola columna log_entry para el archivo seleccionado.

    postgres=> SELECT create_foreign_table_for_log_file('my_postgres_error_log', 'log_server', 'postgresql.log.2016-08-09-22.csv');

    La respuesta no proporciona más detalles que el hecho de que la tabla ya existe.

    ----------------------------------- (1 row)
  5. Seleccione una muestra del archivo de registro. El siguiente código recupera la hora del registro y la descripción del mensaje de error.

    postgres=> SELECT log_time, message FROM my_postgres_error_log ORDER BY 1;

    A continuación, se muestra una respuesta de ejemplo.

    log_time | message ----------------------------------+--------------------------------------------------------------------------- Tue Aug 09 15:45:18.172 2016 PDT | ending log output to stderr Tue Aug 09 15:45:18.175 2016 PDT | database system was interrupted; last known up at 2016-08-09 22:43:34 UTC Tue Aug 09 15:45:18.223 2016 PDT | checkpoint record is at 0/90002E0 Tue Aug 09 15:45:18.223 2016 PDT | redo record is at 0/90002A8; shutdown FALSE Tue Aug 09 15:45:18.223 2016 PDT | next transaction ID: 0/1879; next OID: 24578 Tue Aug 09 15:45:18.223 2016 PDT | next MultiXactId: 1; next MultiXactOffset: 0 Tue Aug 09 15:45:18.223 2016 PDT | oldest unfrozen transaction ID: 1822, in database 1 (7 rows)

Uso de la extensión postgres_fdw para acceder a datos externos

Puede acceder a los datos en una tabla en un servidor de base de datos remoto con la extensión postgres_fdw. Si establece una conexión remota desde su instancia de base de datos de PostgreSQL, el acceso también está disponible para su réplica de lectura.

Para utilizar postgres_fdw para acceder a un servidor de base de datos remoto

  1. Instale la extensión postgres_fdw.

    CREATE EXTENSION postgres_fdw;
  2. Cree el servidor de datos externo utilizando CREATE SERVER.

    CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'xxx.xx.xxx.xx', port '5432', dbname 'foreign_db');
  3. Cree un mapeo de usuario para identificar la función que utilizar en el servidor remoto.

    CREATE USER MAPPING FOR local_user SERVER foreign_server OPTIONS (user 'foreign_user', password 'password');
  4. Cree una tabla que se mapee a la tabla del servidor remoto.

    CREATE FOREIGN TABLE foreign_table ( id integer NOT NULL, data text) SERVER foreign_server OPTIONS (schema_name 'some_schema', table_name 'some_table');

Uso de bases de datos MySQL con la extensión mysql_fdw

Para acceder a una base de datos compatible con MySQL desde su instancia de base de datos de RDS for PostgreSQL, puede instalar y utilizar la extensión mysql_fdw. Este contenedor de datos externo le permite trabajar con RDS for MySQL, Aurora MySQL, MariaDB y otras bases de datos compatibles con MySQL. La conexión desde RDS for PostgreSQL a la base de datos MySQL se cifra de la mejor manera posible, según las configuraciones del cliente y del servidor. No obstante, puede aplicar cifrado si lo desea. Para obtener más información, consulte Uso de cifrado en tránsito con la extensión .

La extensión mysql_fdw es compatible con las versiones 14.2, 13.6 y posteriores de Amazon RDS for PostgreSQL. Es compatible con selecciones, inserciones, actualizaciones y eliminaciones de una base de datos de RDS for PostgreSQL en tablas de una instancia de base de datos compatible con MySQL.

Configuración de su base de datos RDS for PostgreSQL para utilizar la extensión mysql_fdw

Configurar la extensión mysql_fdw en la instancia de base de datos de RDS for PostgreSQL implica cargar la extensión en la instancia de base de datos y, a continuación, crear el punto de conexión a la instancia de base de datos MySQL. Para esa tarea debe disponer de los siguientes detalles sobre la instancia de base de datos MySQL:

  • Nombre de host o del punto de conexión. Para una instancia de RDS for MySQL puede encontrar los puntos de conexión con la consola. Elija la pestaña Conectividad y seguridad y busque en la sección “Punto de enlace y puerto”.

  • Número de puerto. El número de puerto predeterminado para MySQL es 3306.

  • Nombre de la base de datos. El identificador de la base de datos.

También tiene que proporcionar acceso en el grupo de seguridad o en la lista de control de acceso (ACL) para el puerto MySQL, 3306. Tanto la instancia de base de datos de RDS for PostgreSQL como la instancia de base de datos de RDS for MySQL necesitan acceso al puerto 3306. Si el acceso no está configurado correctamente, al intentar conectarse a una tabla compatible con MySQL aparecerá un mensaje de error similar al siguiente:

ERROR: failed to connect to MySQL: Can't connect to MySQL server on 'hostname.aws-region.rds.amazonaws.com:3306' (110)

En el procedimiento que sigue, usted (como cuenta de rds_superuser) crea el servidor externo. A continuación, concede acceso al servidor externo a usuarios específicos. A continuación, estos usuarios crean sus propias asignaciones a las cuentas de usuario de MySQL adecuadas para trabajar con la instancia de base de datos MySQL.

Para utilizar mysql_fdw para acceder a un servidor de base de datos MySQL

  1. Conéctese a la instancia de base de datos PostgreSQL a través de una cuenta que tenga el rol de rds_superuser. Si aceptó los valores predeterminados al crear la instancia de base de datos de RDS for PostgreSQL, el nombre de usuario espostgres, y se podrá conectar a través de la herramienta de línea de comandos psql como sigue:

    psql --host=your-DB-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres –-password
  2. Instale la extensión mysql_fdw de la siguiente manera:

    postgres=> CREATE EXTENSION mysql_fdw; CREATE EXTENSION

Después de instalar la extensión en la instancia de base de datos de RDS for PostgreSQL, configure el servidor externo que proporciona la conexión a una base de datos MySQL.

Para crear el servidor externo

Realice estas tareas en la instancia de base de datos de RDS for PostgreSQL. Para seguir estos pasos se entiende que está conectado como usuario con privilegios rds_superuser, como postgres.

  1. Cree un servidor externo en la instancia de base de datos de RDS for PostgreSQL:

    postgres=> CREATE SERVER mysql-db FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'db-name.111122223333.aws-region.rds.amazonaws.com', port '3306'); CREATE SERVER
  2. Conceda a los usuarios que corresponsa acceso al servidor externo. Deben ser usuarios que no sean administradores, es decir, usuarios que no tengan el rol rds_superuser.

    postgres=> GRANT USAGE ON FOREIGN SERVER mysql-db to user1; GRANT

Los usuarios de PostgreSQL crean y administran sus propias conexiones a la base de datos MySQL a través del servidor externo.

Ejemplo: Trabajar con una base de datos de RDS para una base de datos MySQL desde RDS for PostgreSQL

Supongamos que tiene una tabla simple en una instancia de base de datos de RDS for MySQL. Los usuarios de RDS for PostgreSQL desean consultar los elementos (SELECT), INSERT, UPDATE yDELETE en la tabla. Supongamos que la exstensión mysql_fdw se creó en la instancia de base de datos de RDS for PostgreSQL, como se detalla en el procedimiento anterior. Después de conectarse a la instancia de base de datos de RDS for PostgreSQL como usuario con privilegios rds_superuser, podrá continuar con los pasos que se describen a continuación.

  1. Cree un servidor externo en la instancia de base de datos de RDS for PostgreSQL:

    test=> CREATE SERVER mysqldb FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'your-DB.aws-region.rds.amazonaws.com', port '3306'); CREATE SERVER
  2. Conceda permiso de uso a un usuario que no tiene permisos rds_superuser, por ejemplo user1.

    test=> GRANT USAGE ON FOREIGN SERVER mysqldb TO user1; GRANT
  3. Conéctese comouser1 y, a continuación, cree una asignación para el usuario de MySQL:

    test=> CREATE USER MAPPING FOR user1 SERVER mysqldb OPTIONS (username 'myuser', password 'mypassword'); CREATE USER MAPPING
  4. Cree una tabla externa vinculada a la tabla MySQL:

    test=> CREATE FOREIGN TABLE mytab (a int, b text) SERVER mysqldb OPTIONS (dbname 'test', table_name ''); CREATE FOREIGN TABLE
  5. Ejecute una consulta simple en la tabla externa:

    test=> SELECT * FROM mytab; a | b ---+------- 1 | apple (1 row)
  6. Puede añadir, modificar y quitar datos de la tabla MySQL. Por ejemplo:

    test=> INSERT INTO mytab values (2, 'mango'); INSERT 0 1

    Ejecute la consulta SELECT de nuevo para ver los resultados:

    test=> SELECT * FROM mytab ORDER BY 1; a | b ---+------- 1 | apple 2 | mango (2 rows)

Uso de cifrado en tránsito con la extensión

La conexión a MySQL desde RDS for PostgreSQL utiliza cifrado en tránsito (TLS/SSL) de forma predeterminada. No obstante, la conexión vuelve a ser no cifrada cuando la configuración del cliente y del servidor difieren. Puede aplicar el cifrado para todas las conexiones salientes especificando la opción REQUIRE SSL en las cuentas de usuario de RDS for MySQL. Este mismo método también funciona para las cuentas de usuario de MariaDB y Aurora MySQL.

Para cuentas de usuario MySQL configuradas en REQUIRE SSL, el intento de conexión falla si no se puede establecer una conexión segura.

Para aplicar el cifrado de cuentas de usuario de bases de datos MySQL existentes, puede utilizar el comando ALTER USER. La sintaxis varía en función de la versión de MySQL, como se muestra en la siguiente tabla. Para obtener más información, consulte ALTER USER en el manual de referencia de MySQL.

De MySQL 5.7 a MySQL 8 MySQL 5.6

ALTER USER 'user'@'%' REQUIRE SSL;

GRANT USAGE ON *.* to 'user'@'%' REQUIRE SSL;

Para obtener más información acerca de la extensión mysql_fdw, consulte la documentación sobre mysql_fdw.

Uso de una base de datos de Oracle con la extensión oracle_fdw

Para acceder a una base de datos de Oracle desde su instancia de base de datos de RDS for PostgreSQL puede instalar y utilizar la extensión oracle_fdw. Esta extensión es un contenedor de datos externos para bases de datos Oracle. Para obtener más información sobre la extensión, consulte la documentación de oracle_fdw.

La extensión oracle_fdw es compatible con las versiones 12.7, 13.3 y las versiones posteriores de RDS for PostgreSQL.

Activación de la extensión oracle_fdw

Para utilizar la extensión oracle_fdw, lleve a cabo el siguiente procedimiento.

Para habilitar la extensión oracle_fdw

  • Ejecute el siguiente comando con una cuenta que tenga los permisos rds_superuser.

    CREATE EXTENSION oracle_fdw;

Ejemplo: Usar un servidor externo vinculado a una Amazon RDS for Oracle Database

El siguiente ejemplo muestra el uso de un servidor externo vinculado a una base de datos de Amazon RDS for Oracle.

Crear un servidor externo vinculado a una base de datos de RDS for Oracle

  1. Tenga en cuenta lo siguiente en la instancia de base de datos de RDS for Oracle:

    • punto de enlace

    • Puerto

    • Nombre de base de datos

  2. Cree un servidor externo.

    test=> CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//endpoint:port/DB_name'); CREATE SERVER
  3. Otorgue uso a un usuario que no tenga permisos rds_superuser, por ejemplo user1.

    test=> GRANT USAGE ON FOREIGN SERVER oradb TO user1; GRANT
  4. Conéctese como user1 y cree una asignación a un usuario de Oracle.

    test=> CREATE USER MAPPING FOR user1 SERVER oradb OPTIONS (user 'oracleuser', password 'mypassword'); CREATE USER MAPPING
  5. Cree una tabla externa vinculada a una tabla de Oracle.

    test=> CREATE FOREIGN TABLE mytab (a int) SERVER oradb OPTIONS (table 'MYTABLE'); CREATE FOREIGN TABLE
  6. Consulte la tabla externa.

    test=> SELECT * FROM mytab; a --- 1 (1 row)

Si la consulta informa el siguiente error, verifique el grupo de seguridad y la lista de control de acceso (ACL) para asegurarse de que ambas instancias puedan comunicarse.

ERROR: connection for foreign table "mytab" cannot be established DETAIL: ORA-12170: TNS:Connect timeout occurred

Trabajo con cifrado en tránsito

El cifrado de PostgreSQL a Oracle en tránsito se basa en una combinación de parámetros de configuración de cliente y servidor. Para obtener un ejemplo que utiliza Oracle 21c, consulte About the Values for Negotiating Encryption and Integrity en la documentación de Oracle. El cliente utilizado para oracle_fdw en Amazon RDS está configurado con ACCEPTED, lo que significa que el cifrado depende de la configuración del servidor de base de datos de Oracle.

Si su base de datos está en RDS for Oracle, consulte Oracle Native Network Encryption para configurar el cifrado.

Comprensión y permisos de la vista pg_user_mappings

El catálogo de PostgreSQL pg_user_mapping almacena la asignación desde un usuario RDS for PostgreSQL en el usuario de un servidor de datos externo (remoto). El acceso al catálogo está restringido, pero usted utiliza la vista pg_user_mappings para ver las asignaciones. A continuación, se muestra un ejemplo sobre cómo se aplican los permisos en una base de datos de Oracle de ejemplo, aunque esta información es válida también en general para cualquier contenedor de datos externo.

En el siguiente resultado, puede encontrar roles y permisos asignados a tres usuarios de ejemplo diferentes. Usuarios de rdssu1 y rdssu2 son miembros del rol rds_superuser, y el usuario user1 no lo es. En el ejemplo se usa el metacomando psql de \du para enumerar los roles existentes.

test=> \du List of roles Role name | Attributes | Member of -----------------+------------------------------------------------------------+------------------------------------------------------------- rdssu1 | | {rds_superuser} rdssu2 | | {rds_superuser} user1 | | {}

Todos los usuarios, incluidos los usuarios con privilegios rds_superuser, pueden ver sus propias asignaciones de usuarios (umoptions) en la tabla pg_user_mappings. Como se muestra en el siguiente ejemplo, cuando rdssu1 intenta obtener todas las asignaciones de usuario, se genera un a pesar de los privilegios rds_superuser de rdssu1:

test=> SELECT * FROM pg_user_mapping; ERROR: permission denied for table pg_user_mapping

A continuación, se muestran algunos ejemplos:

test=> SET SESSION AUTHORIZATION rdssu1; SET test=> SELECT * FROM pg_user_mappings; umid | srvid | srvname | umuser | usename | umoptions -------+-------+---------+--------+------------+---------------------------------- 16414 | 16411 | oradb | 16412 | user1 | 16423 | 16411 | oradb | 16421 | rdssu1 | {user=oracleuser,password=mypwd} 16424 | 16411 | oradb | 16422 | rdssu2 | (3 rows) test=> SET SESSION AUTHORIZATION rdssu2; SET test=> SELECT * FROM pg_user_mappings; umid | srvid | srvname | umuser | usename | umoptions -------+-------+---------+--------+------------+---------------------------------- 16414 | 16411 | oradb | 16412 | user1 | 16423 | 16411 | oradb | 16421 | rdssu1 | 16424 | 16411 | oradb | 16422 | rdssu2 | {user=oracleuser,password=mypwd} (3 rows) test=> SET SESSION AUTHORIZATION user1; SET test=> SELECT * FROM pg_user_mappings; umid | srvid | srvname | umuser | usename | umoptions -------+-------+---------+--------+------------+-------------------------------- 16414 | 16411 | oradb | 16412 | user1 | {user=oracleuser,password=mypwd} 16423 | 16411 | oradb | 16421 | rdssu1 | 16424 | 16411 | oradb | 16422 | rdssu2 | (3 rows)

Debido a las diferencias en la implementación de information_schema._pg_user_mappings y pg_catalog.pg_user_mappings, un rds_superuser que se crea manualmente requiere permisos adicionales para ver las contraseñas en pg_catalog.pg_user_mappings.

No requieren otros permisos para un rds_superuser para ver las contraseñas en information_schema._pg_user_mappings.

Los usuarios que no tienen el rol rds_superuser pueden ver contraseñas en pg_user_mappings solo en las condiciones que se describen a continuación:

  • El usuario actual es el usuario que se está asignando y es el propietario del servidor o tiene el privilegio de USAGE en él.

  • El usuario actual es el propietario del servidor, y la asignación es para PUBLIC.

Uso de bases de datos de SQL Server con la extensión mysql_fdw

Puede utilizar la extensión de PostgreSQL tds_fdw para acceder a bases de datos compatibles con el protocolo de flujo de datos tabular (TDS), como bases de datos Sybase y Microsoft SQL Server. Este contenedor de datos externo le permite conectarse desde suinstancia de base de datos RDS for PostgreSQL a bases de datos que utilizan el protocolo TDS, incluido Amazon RDS for Microsoft SQL Server. Para obtener más información, consulte la documentación sobre tds-fdw/tds_fdw en GitHub.

La extensión tds_fdw es compatible con las versiones 14.2, 13.6 y posteriores de Amazon RDS for PostgreSQL.

Configuración de la base de datos RDS for PostgreSQL para utilizar la extensión mysql_fdw

En los procedimientos que siguen encontrará un ejemplo de configuración y uso de tds_fdw con una instancia de base de datos RDS for PostgreSQL. Antes de poder conectarse a una base de datos SQL Server mediante tds_fdw, tiene que obtener los siguientes detalles de la instancia:

  • Nombre de host o del punto de conexión. Para instancias de RDS for MySQL encontrará los puntos de conexión con la consola. Elija la pestaña Conectividad y seguridad y busque en la sección “Punto de enlace y puerto”.

  • Número de puerto. El puerto 1433 es el predeterminado para Microsoft SQL Server.

  • Nombre de la base de datos. El identificador de la base de datos.

También deberá proporcionar acceso en el grupo de seguridad o en la lista de control de acceso (ACL) al puerto MySQL, 1433. Tanto el clúster de bases de datos de Aurora PostgreSQL como necesitan poder acceder al puerto 1433. Si el acceso no está configurado correctamente, cuando intente consultar Microsoft SQL Server aparecerá el siguiente mensaje de error:

ERROR: DB-Library error: DB #: 20009, DB Msg: Unable to connect: Adaptive Server is unavailable or does not exist (mssql2019.aws-region.rds.amazonaws.com), OS #: 0, OS Msg: Success, Level: 9

Para usar tds_fdw para conectarse a una base de datos de SQL Server

  1. Conéctese a su instancia de base de datos PostgreSQL con una cuenta con rol rds_superuser:

    psql --host=your-DB-instance.aws-region.rds.amazonaws.com --port=5432 --username=test –-password
  2. Instale la extensión tds_fdw.

    test=> CREATE EXTENSION tds_fdw; CREATE EXTENSION

Después de instalar la extensión en su instancia de base de datos RDS for PostgreSQL, configure el servidor externo.

Para crear el servidor externo

Realice estas tareas en la instancia de base de datos RDS for PostgreSQL con una cuenta que con privilegios rds_superuser.

  1. Cree un servidor externo en la instancia de base de datos RDS for PostgreSQL:

    test=> CREATE SERVER sqlserverdb FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'mssql2019.aws-region.rds.amazonaws.com, port '1433', database 'tds_fdw_testing'); CREATE SERVER
  2. Conceda permisos a un usuario que no tenga los privilegios del rol rds_superuser, por ejemplo user1:

    test=> GRANT USAGE ON FOREIGN SERVER sqlserverdb TO user1;
  3. Conéctese como user1 y, a continuación, cree una asignación para el usuario de SQL Server:

    test=> CREATE USER MAPPING FOR user1 SERVER sqlserverdb OPTIONS (username 'sqlserveruser', password 'password'); CREATE USER MAPPING
  4. Cree una tabla externa vinculada a una tabla de SQL Server.

    test=> CREATE FOREIGN TABLE mytab (a int) SERVER sqlserverdb OPTIONS (table 'MYTABLE'); CREATE FOREIGN TABLE
  5. Consulte la tabla externa:

    test=> SELECT * FROM mytab; a --- 1 (1 row)

Uso de cifrado en tránsito para la conexión

La conexión de RDS for PostgreSQLa SQL Server utiliza cifrado en tránsito (TLS/SSL) según la configuración de la base de datos de SQL Server. Si SQL Server no está configurado para el cifrado, el RDS para el cliente PostgreSQL que realiza la solicitud a la base de datos de SQL Server vuelve a no ir cifrado.

Puede aplicar el cifrado para la conexión a RDS para instancias de base de datos de SQL Server configurando el parámetro rds.force_ssl. Para saber cómo, consulte Requerir que las conexiones a la instancia de base de datos usen SSL. Para obtener más información sobre la configuración de SSL/TLS para RDS for SQL Server, consulte Uso de SSL con una instancia de base de datos de Microsoft SQL Server.