Postgresql – Creating a read only account for AWS RDS PostgreSQL

amazon-rdsamazon-web-servicespostgresql

I would like to create a read only account for all of our RDS instances in AWS (PostGreSQL). I started by creating an IAM group and attached the AmazonRDSReadOnlyAccess policy. I created a new IAM user and put him in this group. I cannot connect to our database with this new read only user (tested successfully with our admin account just fine). I now realize I don't have a read only user in the database itself. Which begs me three questions:

  1. Is there any sense in creating a read-only IAM user for our RDS
    instances when I can create a read only DB user inside the database?
  2. When I access the db via the commandline, ex. psql –host=servername –port=portnumber –username=username –password –dbname=databasename. This is the name of the DB user as I understand, so is there a point in creating a separate RDS IAM read
    only user?
  3. If I have a read only IAM user for RDS, is there any danger they could still have write access if say someone else created a DB write
    account for them?

Best Answer

IAM users and DB users are 100% separated. One never affects the other and each serves different purposes.

IAM users

These users read/change/delete the servers using the AWS APIs. They cannot access or otherwise do anything with the database via the psql application.

IAM users can do the following:

  • Create RDS instances
  • Read information from the RDS instance
  • Delete RDS instances
  • Create and restore snapshots
  • Modify RDS options and parameters

All the above are done using the AWS SDKs and CLI.

DB users

These users read/change/delete data in the database. They cannot manage or otherwise do anything with the RDS instance.

DB users can do the following:

  • Select data
  • Insert data
  • Update data
  • Delete data
  • Create databases
  • Managed other DB users

All the above are done using psql for example.

Related Topic