ALTER ROLE query is always ran

[lieutdan13]

I'm not sure if this is a puppetlabs-puppetdb or a puppetlabs-postgresql bug, but...

Every time I run the puppetdb class with or without a specified password, the "ALTER ROLE" query is always run. This could be because the puppetdb user doesn't have proper access to the tables to check for user existence?

[kbarber]

Its possibly postgresql but hard to say - can you show the full output of your run please?

[jtopjian]

I'm running into this as well:

Notice: /Stage[main]/Puppetdb::Database::Postgresql_db/Postgresql::Db[puppetdb]/Postgresql::Database_user[puppetdb]/Postgresql::Role[puppetdb]/Postgresql_psql[ALTER ROLE "puppetdb" ENCRYPTED PASSWORD 'puppetdb']/command: command changed '' to 'ALTER ROLE "puppetdb" ENCRYPTED PASSWORD 'puppetdb''
Notice: Finished catalog run in 4.17 seconds

[jtopjian]

I don't think it's a permissions issue. I think it has to do with the query being used to verify the user's existence in the pg_shadow table.

See line 82 of postgresql/manifests/role.pp:

The unless is trying to verify user puppetdb exists with a passwd of $password_hash. $password_hash is simply the unhashed password passed down by the puppetdb module -- no hashing is ever done.

If the unless query is modified to:

unless => "SELECT usename FROM pg_shadow WHERE usename='${username}' and passwd = 'md5' || md5('${password_hash}' || '${username}')"

Then everything works.

So I think this is a bug with the postgresql module?

[lieutdan13]

@kbarber My output is exactly/similar to jtopjian's.

@jtopjian That is what I had found as well. Have you tested the change to the unless parameter? Perhaps you or I could open a bug with the possible fix.

[kbarber]

@jtopjian @lieutdan13 yeah, sounds like postgresql. You guys want to re-open this in that project? BTW - I get the feeling something was merged in recently to solve this ... have a try at the latest postgresql, or 'master' from github.

[jtopjian]

@lieutdan13 Yes, I tested the change and it looks good. I'll be in meetings for the next 4 hours, so if you're able to open a bug before that, please do. :)

@kbarber I'm testing against the latest postgresql module.

[lieutdan13]

@kbarber I am also using the latest postgresql module. I updated this morning.

@jtopjian I will open a bug in that project

Update: postgresql Issue: puppetlabs/puppetlabs-postgresql#216

[kbarber]

Actually, I should keep this open until the source issue is solved. If only for tracking purposes.

[kbarber]

This is fixed with release 2.4.1 of puppetlabs/postgresql.

[pedrocr]

I have the same issue running 2.4.1. I defined this role:

  postgresql::role {'pedrocr':
    superuser => true,
    createdb => true,
    login => true,
    require => Class["common::postgresql"],
  }

and common::postgresql is just:

class common::postgresql {
  class { '::postgresql':
    charset => 'UTF8',
    locale  => 'en_US.UTF-8',
  }
  class { '::postgresql::server':
  }
  postgresql::database { ['template0', 'template1', 'postgres']:
    update_charset => true,
  }
}

(it would be just "include postgresql::database" if it wasn't for bug #196)

Here's the output of the run:

Notice: /Stage[main]/Battlestation::Databases/Postgresql::Role[pedrocr]/Postgresql_psql[ALTER ROLE "pedrocr" ]/command: command changed '' to 'ALTER ROLE "pedrocr" '

Could it be that the fix doesn't work if the role doesn't have a password at all?