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

Question

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 ?

iBehr · Answer

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= \
   --port= \
   --username= \
   --password \
   --dbname= 
```

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

Pouleto · Answer

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...

marina · Answer

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

Pouleto · Answer

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?

Ravi · Answer

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.

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

Relevant content