PAM authentication failed for user

[Anton-Shutik]

Terraform Version

Terraform v1.3.9
on darwin_arm64

Affected Resource(s)

• postgresql_database

Terraform Configuration Files

provider "postgresql" {
  scheme = "awspostgres"

  host      = "xxx.us-east-1.rds.amazonaws.com"
  port      = 5432
  database  = "postgres"
  username  = "terraformuser"
  password  = jsondecode(data.sops_file.sops_passwords.raw)["passwords"]["terraformuser"]
  sslmode   = "require"
  superuser = false
}

resource "postgresql_role" "roles" {

  name     = "new_database_owner"
  login    = true
  password = jsondecode(data.sops_file.sops_passwords.raw)["passwords"]["new_database_owner"]
  roles    = ["rds_iam"]
  create_database = true
}

resource "postgresql_database" "databases" {

  name  = "new_database"
  owner = "new_database_owner"

}

resource "postgresql_grant" "grants" {
  ...
}

Debug Output

Error: error detecting capabilities: error PostgreSQL version: pq: PAM authentication failed for user "terraformuser"

RDS postgres logs

Logs

2023-03-10 11:10:07 UTC:82.214.175.74(13708):[unknown]@[unknown]:[835]:LOG:  connection received: host=82.214.175.74 port=13708
2023-03-10 11:10:07 UTC:82.214.175.74(13708):terraformuser@postgres:[835]:LOG:  connection authenticated: identity="terraformuser" method=md5 (/rdsdbdata/config/pg_hba.conf:15)
2023-03-10 11:10:07 UTC:82.214.175.74(13708):terraformuser@postgres:[835]:LOG:  connection authorized: user=terraformuser database=postgres application_name=Terraform provider SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, bits=256)
2023-03-10 11:10:07 UTC:82.214.175.74(13708):terraformuser@postgres:[835]:LOG:  statement: SELECT VERSION()
2023-03-10 11:10:07 UTC:82.214.175.74(13708):terraformuser@postgres:[835]:LOG:  duration: 0.428 ms
2023-03-10 11:10:07 UTC:82.214.175.74(13708):terraformuser@postgres:[835]:LOG:  disconnection: session time: 0:00:00.847 user=terraformuser database=postgres host=82.214.175.74 port=13708


2023-03-10 11:10:08 UTC:82.214.175.74(9156):[unknown]@[unknown]:[836]:LOG:  connection received: host=82.214.175.74 port=9156
2023-03-10 11:10:09 UTC:82.214.175.74(9156):terraformuser@postgres:[836]:LOG:  connection authenticated: identity="terraformuser" method=md5 (/rdsdbdata/config/pg_hba.conf:15)
2023-03-10 11:10:09 UTC:82.214.175.74(9156):terraformuser@postgres:[836]:LOG:  connection authorized: user=terraformuser database=postgres application_name=Terraform provider SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, bits=256)
2023-03-10 11:10:09 UTC:82.214.175.74(9156):terraformuser@postgres:[836]:LOG:  statement: BEGIN READ WRITE
2023-03-10 11:10:09 UTC:82.214.175.74(9156):terraformuser@postgres:[836]:LOG:  duration: 0.148 ms
2023-03-10 11:10:09 UTC:82.214.175.74(9156):terraformuser@postgres:[836]:LOG:  statement: SET statement_timeout = 0
2023-03-10 11:10:09 UTC:82.214.175.74(9156):terraformuser@postgres:[836]:LOG:  duration: 0.111 ms
2023-03-10 11:10:09 UTC:82.214.175.74(9156):terraformuser@postgres:[836]:LOG:  duration: 0.621 ms  parse <unnamed>: SELECT pg_advisory_xact_lock(oid::bigint) FROM pg_roles WHERE rolname = $1
2023-03-10 11:10:09 UTC:82.214.175.74(9156):terraformuser@postgres:[836]:LOG:  duration: 0.634 ms  bind <unnamed>: SELECT pg_advisory_xact_lock(oid::bigint) FROM pg_roles WHERE rolname = $1
2023-03-10 11:10:09 UTC:82.214.175.74(9156):terraformuser@postgres:[836]:DETAIL:  parameters: $1 = 'terraformuser'
2023-03-10 11:10:09 UTC:82.214.175.74(9156):terraformuser@postgres:[836]:LOG:  execute <unnamed>: SELECT pg_advisory_xact_lock(oid::bigint) FROM pg_roles WHERE rolname = $1
2023-03-10 11:10:09 UTC:82.214.175.74(9156):terraformuser@postgres:[836]:DETAIL:  parameters: $1 = 'terraformuser'
2023-03-10 11:10:09 UTC:82.214.175.74(9156):terraformuser@postgres:[836]:LOG:  duration: 2.281 ms
2023-03-10 11:10:09 UTC:82.214.175.74(9156):terraformuser@postgres:[836]:LOG:  duration: 0.158 ms  parse <unnamed>: SELECT pg_advisory_xact_lock(member::bigint) FROM pg_auth_members JOIN pg_roles ON roleid = pg_roles.oid WHERE rolname = $1
2023-03-10 11:10:09 UTC:82.214.175.74(9156):terraformuser@postgres:[836]:LOG:  duration: 0.460 ms  bind <unnamed>: SELECT pg_advisory_xact_lock(member::bigint) FROM pg_auth_members JOIN pg_roles ON roleid = pg_roles.oid WHERE rolname = $1
2023-03-10 11:10:09 UTC:82.214.175.74(9156):terraformuser@postgres:[836]:DETAIL:  parameters: $1 = 'terraformuser'
2023-03-10 11:10:09 UTC:82.214.175.74(9156):terraformuser@postgres:[836]:LOG:  execute <unnamed>: SELECT pg_advisory_xact_lock(member::bigint) FROM pg_auth_members JOIN pg_roles ON roleid = pg_roles.oid WHERE rolname = $1
2023-03-10 11:10:09 UTC:82.214.175.74(9156):terraformuser@postgres:[836]:DETAIL:  parameters: $1 = 'terraformuser'
2023-03-10 11:10:09 UTC:82.214.175.74(9156):terraformuser@postgres:[836]:LOG:  duration: 0.040 ms


2023-03-10 11:10:10 UTC:82.214.175.74(9991):[unknown]@[unknown]:[840]:LOG:  connection received: host=82.214.175.74 port=9991
2023-03-10 11:10:11 UTC:82.214.175.74(9991):terraformuser@postgres:[840]:LOG:  connection authenticated: identity="terraformuser" method=md5 (/rdsdbdata/config/pg_hba.conf:15)
2023-03-10 11:10:11 UTC:82.214.175.74(9991):terraformuser@postgres:[840]:LOG:  connection authorized: user=terraformuser database=postgres application_name=Terraform provider SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, bits=256)
2023-03-10 11:10:11 UTC:82.214.175.74(9991):terraformuser@postgres:[840]:LOG:  duration: 0.351 ms  parse <unnamed>: SELECT 1 FROM pg_auth_members WHERE pg_get_userbyid(roleid) = $1 AND pg_get_userbyid(member) = $2
2023-03-10 11:10:11 UTC:82.214.175.74(9991):terraformuser@postgres:[840]:LOG:  duration: 0.411 ms  bind <unnamed>: SELECT 1 FROM pg_auth_members WHERE pg_get_userbyid(roleid) = $1 AND pg_get_userbyid(member) = $2
2023-03-10 11:10:11 UTC:82.214.175.74(9991):terraformuser@postgres:[840]:DETAIL:  parameters: $1 = 'new_database_owner', $2 = 'terraformuser'
2023-03-10 11:10:11 UTC:82.214.175.74(9991):terraformuser@postgres:[840]:LOG:  execute <unnamed>: SELECT 1 FROM pg_auth_members WHERE pg_get_userbyid(roleid) = $1 AND pg_get_userbyid(member) = $2
2023-03-10 11:10:11 UTC:82.214.175.74(9991):terraformuser@postgres:[840]:DETAIL:  parameters: $1 = 'new_database_owner', $2 = 'terraformuser'
2023-03-10 11:10:11 UTC:82.214.175.74(9991):terraformuser@postgres:[840]:LOG:  duration: 0.098 ms
2023-03-10 11:10:11 UTC:82.214.175.74(9991):terraformuser@postgres:[840]:LOG:  disconnection: session time: 0:00:00.922 user=terraformuser database=postgres host=82.214.175.74 port=9991

2023-03-10 11:10:12 UTC:82.214.175.74(7812):[unknown]@[unknown]:[852]:LOG:  connection received: host=82.214.175.74 port=7812
2023-03-10 11:10:12 UTC:82.214.175.74(7812):terraformuser@postgres:[852]:LOG:  connection authenticated: identity="terraformuser" method=md5 (/rdsdbdata/config/pg_hba.conf:15)
2023-03-10 11:10:12 UTC:82.214.175.74(7812):terraformuser@postgres:[852]:LOG:  connection authorized: user=terraformuser database=postgres application_name=Terraform provider SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, bits=256)
2023-03-10 11:10:12 UTC:82.214.175.74(7812):terraformuser@postgres:[852]:LOG:  statement: GRANT "new_database_owner" TO "terraformuser"
2023-03-10 11:10:12 UTC:82.214.175.74(7812):terraformuser@postgres:[852]:LOG:  duration: 4.235 ms
2023-03-10 11:10:12 UTC:82.214.175.74(7812):terraformuser@postgres:[852]:LOG:  disconnection: session time: 0:00:00.816 user=terraformuser database=postgres host=82.214.175.74 port=7812

2023-03-10 11:10:13 UTC:82.214.175.74(5922):[unknown]@[unknown]:[853]:LOG:  connection received: host=82.214.175.74 port=5922
*   Trying 127.0.0.1:1108...
* Connected to rdsauthproxy (127.0.0.1) port 1108 (#0)
> POST /authenticateRequest HTTP/1.1
Host: rdsauthproxy:1108
Accept: */*
Content-Length: 509
Content-Type: multipart/form-data; boundary=------------------------fe102b81b0efb325

* We are completely uploaded and fine
* Mark bundle as not supporting multiuse
< HTTP/1.1 403 Forbidden
< Content-Type: text/html;charset=utf-8
< Content-Length: 0
< 
* Connection #0 to host rdsauthproxy left intact
2023-03-10 11:10:13 UTC:82.214.175.74(5922):terraformuser@postgres:[853]:LOG:  pam_authenticate failed: Permission denied
2023-03-10 11:10:13 UTC:82.214.175.74(5922):terraformuser@postgres:[853]:FATAL:  PAM authentication failed for user "terraformuser"
2023-03-10 11:10:13 UTC:82.214.175.74(5922):terraformuser@postgres:[853]:DETAIL:  Connection matched pg_hba.conf line 13: "hostssl    all             +rds_iam             all            pam"

2023-03-10 11:10:15 UTC:82.214.175.74(9156):terraformuser@postgres:[836]:LOG:  statement: ROLLBACK
2023-03-10 11:10:15 UTC:82.214.175.74(9156):terraformuser@postgres:[836]:LOG:  duration: 0.142 ms
2023-03-10 11:10:15 UTC:82.214.175.74(9156):terraformuser@postgres:[836]:LOG:  disconnection: session time: 0:00:06.767 user=terraformuser database=postgres host=82.214.175.74 port=9156

Expected Behavior

All the resources on the postges server are in sync with terraform config

Actual Behavior

When communicating with postgres server and creating new database with OWNER other than provider's user it fails if that database OWNER has rds_iam role (which require IAM auth rather than password).
That happens because provider user temporarily GRANTs the OWNER to itself in order to run CREATE DATABASE <name> WITH OWNER <OWNER>;. The problem is that provider grants the new database OWNER in one connection, and in the other it tries to connect to run CREATE DATABASE.... query. But it cannot be done, since provider has to authenticate with IAM already, and thus, fails. And then can't REVOKE that membership back for same reason.

Are there any options to avoid this ? That what I tried to do in database and it worked:

psql -h <host> -U terraformuser -d postgres

GRANT new_database_owner TO terraformuser;
CREATE DATABASE new_database WITH OWNER new_database_owner;
REVOKE new_database_owner FROM terraformuser;

But it should run within same database connection, but not transaction, since we cannot run CREATE DATABASE in transaction.

So, is there any option to manage that database resource using one database connection ?

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

1. terraform apply

References

• https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.html#UsingWithRDS.IAMDBAuth.Limitations

For PostgreSQL, if the IAM role (rds_iam) is added to a user (including the RDS master user), IAM authentication takes precedence over password authentication, so the user must log in as a user.

[Anton-Shutik]

@cyrilgdn

I think following approach might be used to workaround this issue:

connect with default user defined in provider:

SET ROLE database_owner;
CREATE DATABASE database_name;
RESET ROLE;

Does it make a sense ?

[aristosvo]

I'm facing this same issue, trying to find a workaround as this would block the complete usage of IAM on db owners.

First try:

resource "postgresql_role" "owner" {
  name                = "owner_${var.database}"
  skip_reassign_owned = "true"

  lifecycle {
    ignore_changes = [roles]
  }
}

resource "postgresql_role" "iam_owner" {
  name                = "iam_owner_${var.database}"
  login               = true
  roles               = ["rds_iam", postgresql_role.owner.name]
  skip_reassign_owned = "true"
}

resource "postgresql_database" "database" {
  name       = var.database
  owner      = postgresql_role.owner.name
  lc_collate = "en_US.UTF-8"
  lc_ctype   = "en_US.UTF-8"
}

[senilio]

I'm working around this by granting rds_iam after db creation is done:

resource "postgresql_role" "owner" {
  name     = "db_owner"
  login    = true

  lifecycle {
    ignore_changes = [roles]
  }
}

resource "postgresql_database" "db" {
  name       = "db"
  owner      = postgresql_role.owner.name
}

resource "postgresql_grant_role" "iam" {
  depends_on = [postgresql_database.db]

  role       = postgresql_role.owner.name
  grant_role = "rds_iam"
}

[thejosephstevens]

So, I encountered this this week, my goal here was to completely automate the setup with no bootstrap steps (I did get there). The way that I'm working around it is by using the master user (with a password pulled from AWS Secretsmanager, managed by RDS) to provision a proxy user (with rds_iam and rds_superuser). I then do all subsequent provisioning with that proxy user. All of the subsequent users have rds_iam and are made owner of their DBs directly through the default owners field on the DB resource. This requires using the AWS auth integration for TF, so you may run into #556 , but it does otherwise work.

I don't know if this is possible in a single Terraform module, I ran into issues trying to accomplish that and just split it into separate stages - first a stage to provision a single proxy user for the RDS cluster, and then all subsequent stages separately from that.

The code isn't terrifically interesting, but for the curious:
Proxy User

# Generic provider setup w/ secretsmanager data source to fetch password
# Avoid the `rds_iam` inheritance problem by just not giving `tf_admin` a DB as owner
resource "postgresql_role" "this" {
  name             = "tf_admin"
  login            = true
  roles            = ["rds_superuser", "rds_iam"]
  superuser        = false
  connection_limit = -1
}

User and DB

# cluster_* resources are common cluster-level values
provider "postgresql" {
  host                          = var.cluster_endpoint
  port                          = var.cluster_port
  username                      = var.proxy_role_name # tf_admin from above
  database                      = var.cluster_database_name
  aws_rds_iam_auth              = true
  aws_rds_iam_region            = local.region # note, this also has to be added as an env var due to #556 
  aws_rds_iam_provider_role_arn = local.tf_role_arn # whatever role has permissions to access tf_admin
  sslmode                       = "require"
  connect_timeout               = 15
  superuser                     = false
}
resource "postgresql_role" "this" {
  name             = var.name
  login            = var.login
  roles            = local.roles
  superuser        = false
  connection_limit = var.connection_limit
  inherit          = var.inherit
}
resource "postgresql_database" "this" {
  name     = var.db_name
  owner    = postgresql_role.this.name
}