By using AWS re:Post, you agree to the AWS re:Post Terms of Use

AWS RDS Postgres : can't log to superuser or rdsadmin ?

1

Hello,

I'm trying to understand why the GB-Mo keep growing on my RDS instance without using it. So I tired to check the size of the log files with :

SELECT name, setting AS file_path, (pg_stat_file(setting)).size AS size
FROM pg_settings
WHERE name LIKE 'log_%'
ORDER BY name;

But i can't :

ERROR:  permission denied for function pg_stat_file
SQL state: 42501

It seems that postgres is not superuser but rdsadmin is. So I tired to give postgres "SUPERUSER" role with this :

ALTER USER postgres WITH SUPERUSER;

Of course, postgres is not superuser so :

ERROR:  must be superuser to alter superuser roles or change superuser attribute
SQL state: 42501

My question is, how can I do that? Giving postgres superuser role or using rdsadmin privileges to do the first request that i've tired ?

5 Answers
2

You should connect to the database with the master username and password that you setup when creating the. RDS instance. If you need assistance, see this part of the documentation: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ConnectToPostgreSQLInstance.html

psql \
   --host=<DB instance endpoint> \
   --port=<port> \
   --username=<master username> \
   --password \
   --dbname=<database name> 

If you no longer have the master credentials, you can follow the following process to reset them.
How do I reset the admin user password for my Amazon RDS DB instance?
https://repost.aws/knowledge-center/reset-master-user-password-rds

profile pictureAWS
EXPERT
answered a year ago
profile picture
EXPERT
reviewed a year ago
1

Well, Thank you everyone for answers, but it seems that AWS does not allow to log in as superuser on RDS with Postgres : https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html

As another example, you don't have access to the PostgreSQL superuser account. On RDS for PostgreSQL, the rds_superuser role is the most highly privileged role, and it's granted to postgres at set up time. Whether you're familiar with using PostgreSQL on-premises or completely new to RDS for PostgreSQL, we recommend that you understand the rds_superuser role, and how to work with roles, users, groups, and permissions. For more information, see Understanding PostgreSQL roles and permissions.

So, that might explain why it's impossible for me to log in as superuser.

If someone is trying to install Postgresql on EC2 to access to psql command because the AWS RDS Instance is on VPC with no internet access, here is the way :

  • Search packages: sudo yum search postgresql
  • You need to install postgresql-XX-server: yum install postgresql15-server.x86_64
  • We want to be able to use psql, so we need to initialize the server: sudo su - postgres
  • We'll be the postgres user, then we need to specify: postgresql-setup --initdb
  • You'll then need to start the postgres service, which you can do with the ec2-user, by typing: exit
  • Then start the postgres service: sudo systemctl start postgresql
  • You can check with: systemctl
  • Then reconnect to the postgres user: sudo su - postgres
  • Then type the command to access the database: psql --host=database_endpoint --port=database_post(5432 by default with postgres) --username=user_name --password --dbname=database_name

For me, it's impossible to log as superuser (rdsadmin) :

psql: error: connection to server at "database_endpoint" (ip_adress), port 5432 failed: FATAL:  pg_hba.conf rejects connection for host "ip_adress", user "rdsadmin", database "original_db", SSL encryption
connection to server at "database_endpoint" (ip_adress), port 5432 failed: FATAL:  pg_hba.conf rejects connection for host "ip_adress", user "rdsadmin", database "original_db", no encryption

I can connect with postgres but not rdsadmin. Inbound rules are OK for the security group (0.0.0.0/0), and it's not possible to edit pg_hba.conf with AWS RDS...

answered a year ago
1

Hi iBehr and Pouleto I am wondering if you could help with this problem. It's different but perhaps you have some recommendations?! Appreciate your help :) https://repost.aws/questions/QUKO7vMLXfTb-AqqeKMImJKg/abstract-error-when-converting-rds-mysql-5-7-44-to-mysql-8

answered 9 months ago
0

Hello iBehr and Govind Kumar,

Thank you for your feedback, unfortunatly i'm still not able to connect :

psql: error: could not translate host name "**my_rds_endpoint**" to address: Unknown host

This may be because my RDS instance is not publicly accessible.

I've looked into using this command with an SSH tunnel (with .PEM identification file) but haven't found a solution.

I also looked into installing Postgres on my EC2 instance which is in the same VPC as my RDS instance in order to use this command within the VPC, but it doesn't seem possible: ¨

[ec2-user@**ip_adress**]$ sudo yum install -y postgresql
Last metadata expiration check: 14:50:06 ago on Sun Jun 25 04:40:12 2023.
No match for argument: postgresql

I can't find a solution to my problem, even when I type this command in my CLI pgadmin, I get an error :

original_db-> \c rdsadmin
connection to server on "**ip_adress**", port 53562 failed: FATAL: pg_hba.conf rejects connection for host "**ip_adress**", user "postgres", database "rdsadmin", SSL encryption
connection to server on "**ip_adress**", port 53562 failed: FATAL: pg_hba.conf rejects connection for host "**ip_adress**", user "postgres", database "rdsadmin", no encryption
Previous connection retained

Any idea?

answered a year ago
  • Yes, you should definitely run it from an instance in the VPC and ensure the RDS security group allows the connection from the client.

    Make sure the instance where you are installing the Postgres client has internet access. Either a public subnet with a default route to an Internet Gateway (IGW) or a private subnet with a default route to a NAT Gateway in a public subnet.

  • Thank you, but unfortunatly i'm not able to install Postgres on the EC2 instance and I don't know why actually...

    I did a search on what is available :

    sudo yum search postgresql
    

    Saw this in the available packages : postgresql15.x86_64 : PostgreSQL client programs, so I installed it :

    Installed:
      postgresql15-15.0-1.amzn2023.0.2.x86_64                        postgresql15-private-libs-15.0-1.amzn2023.0.2.x86_64
    
    Complete!
    

    But still, unable to do anything in the EC2 instance :

    Tired psql :

    [ec2-user@ip_adress ~]$ psql
    psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
            Is the server running locally and accepting connections on that socket?
    

    Tired to launch the server :

    [ec2-user@ip_adress ~]$ sudo systemctl start postgresql
    Failed to start postgresql.service: Unit postgresql.service not found.
    

    Tired to restart the server :

    [ec2-user@ip_adress~]$ sudo service postgresql-15 restart
    Redirecting to /bin/systemctl restart postgresql-15.service
    Failed to restart postgresql-15.service: Unit postgresql-15.service not found.
    

    Do you know what can be wrong here?

  • Postgresql is the server process -- you don't need to run that on the client.

    psql is the client process used to connect to the database. When you run it without arguments it tries to connect to a local database via socket. Run it with the specified arguments to connect to a remote database (RDS).

    psql \
       --host=<DB instance endpoint> \
       --port=<port> \
       --username=<master username> \
       --password \
       --dbname=<database name> 
    
0

Even when logged in with master admin, I am getting the error that is

SQL Error [42501]: ERROR: permission denied to drop objects

I am not sure, how to login with superuser or how to grant superuser privilege to master admin.

answered 8 months ago

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