search

cyrilgdn / terraform-provider-postgresql

Terraform PostgreSQL provider
https://www.terraform.io/docs/providers/postgresql/
Mozilla Public License 2.0
397 stars 205 forks source link

PAM authentication failed for user #289

Open Anton-Shutik opened 1 year ago

Anton-Shutik commented 1 year ago

Terraform Version

Terraform v1.3.9
on darwin_arm64

Affected Resource(s)

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 ```sql 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 : 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 : 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 : 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 : 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 : 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 : 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 : 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 : 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 : 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

Anton-Shutik commented 1 year ago

@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 commented 2 months ago

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"
}