Unable to initialize connection to PostgreSQL RDS from MariaDB RDS via JDBC fdw

Question

I run this SQL using root user:

```sql
create server 'my-pg-prod-rds' foreign data wrapper 'postgresql' options (
    HOST 'my-cluster.cluster-ro-xxxx.us-west-2.rds.amazonaws.com',
    DATABASE 'db_in_pg',
    USER 'my-user',
    PASSWORD 'xxxxxx',
    PORT 5432,
    SOCKET '',
    OWNER 'postgres'
);
```

And got `Access denied; you need (at least one of) the SUPER, FEDERATED ADMIN privilege(s) for this operation`.

Can we have FEDERATED_ADMIN privilege for root?
https://mariadb.com/docs/server/ref/mdb/privileges/FEDERATED_ADMIN/

Roy · Answer

The error message you're receiving, "Access denied; you need (at least one of) the SUPER, FEDERATED ADMIN privilege(s) for this operation", suggests that the user you're using to run the SQL command does not have the necessary permissions to create a foreign server in your PostgreSQL database.

The SUPER and FEDERATED ADMIN privileges are special permissions required to create and manage foreign data wrappers and servers in PostgreSQL. These privileges are typically reserved for database administrators or users with elevated access.

To resolve this issue, you have a few options:

Use a user with the required permissions: If you have another user account with the SUPER or FEDERATED ADMIN privilege, try running the SQL command using that account instead of the root user.

Grant the required permissions to the root user: Alternatively, you can grant the necessary permissions to the root user by running the following SQL commands:

GRANT SUPER ON *.* TO 'root'@'%';
    GRANT FEDERATED ADMIN ON *.* TO 'root'@'%';

Replace 'root' with the username you're using to run the SQL command.

Create the foreign server using an alternative method: Instead of using the CREATE SERVER SQL command, you can consider using the AWS Data Migration Service (AWS DMS) or the AWS Glue Data Catalog to create the connection to your PostgreSQL database on Amazon RDS. These services provide a user-friendly interface and may require different sets of permissions.

After granting the required permissions or using an alternative method, try running the CREATE SERVER SQL command again. If the issue persists, you may want to consult the PostgreSQL documentation

Unable to initialize connection to PostgreSQL RDS from MariaDB RDS via JDBC fdw

Contenuto pertinente