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

0

I run this SQL using root user:

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/

asked a month ago46 views
1 Answer
-1

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

answered a month ago
  • Have you ever created a RDS and tried the SQL? Do you know RDS restricted root user privilege? Please! Everytime I ask question here there must be a self-proclaimed expert replying solution that is not working, actually don't even have proper experience nor understanding of the problem.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions