postgresql_database should check for database owner

[leegarrett]

Cookbook version

7.1.1

Chef-client version

root@default-ubuntu-1804:~# chef-client -v
Chef: 13.8.5

Platform Details

Ubuntu 18.04

Scenario:

I noticed that the postgresql_database is not idempotent. For example, changing the database owner will return

         * postgresql_database[icinga2] action create
           * bash[Create Database icinga2] action run (skipped due to not_if)
            (up to date)

even though the database owner is not changed.

Steps to Reproduce:

This is example code in a cookbook to show the issue. The first resource should create a database named "icinga2" with owner "postgres", and the 2nd should change the database owner to "icinga2". It's because the not_if clause only checks for presence of the database, and not if the other attributes match. This issue is related to #533.

postgresql_database 'icinga2' do
  owner 'postgres'
end

postgresql_database 'icinga2' do
  owner 'icinga2'
end

Expected Result:

[What are you expecting to happen as the consequence of above reproduction steps?]

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 icinga2   | icinga2  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

Actual Result:

[What actually happens after the reproduction steps? Include the error output or a link to a gist if possible.]

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 icinga2   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

[damacus]

Interesting!

Few edge case questions:

1. If we change the owner of the database, do we have to-recreate the database?
2. Are you trying to fix-up an existing database?

As the database has already been created, and may contain data. I would suggest, if we have to delete the database, and recreate it with the correct details. We shouldn't do this with Chef and just keep ignoring behaviour so the user doesn't lose data due to an owner changing.

If we don't need to recreate the database, can you expand the not_if clause to include the owner, and any other non-destructive and potentially changeable options?

[damacus]

Changing the title as it technically is idempotent.

If you give it the same block twice it doesn't try to change it.

[leegarrett]

To change the database, only ALTER DATABASE <name> OWNER TO <new_owner> is needed. This command is quick, and doesn't recreate the database.

I just happened to stumble over it while working on the icinga2 cookbooks. It turned out that part of the cookbooks were creating the database automatically, and I was wondering why the owner didn't match with the one I defined. The cookbook snippet above is just a simple case to show the issue.

I kept the title more general because I'm guessing that the other options won't take effect either if the database already exists (I didn't check).

[josephholsten]

@leegarrett do you think you could give a shot at implementing this? I'm not sure anyone else has the time right yet, but it sounds like a useful feature.

[github-actions[bot]]

Marking stale due to inactivity. Remove stale label or comment or this will be closed in 7 days. Alternatively drop by the #sous-chefs channel on the Chef Community Slack and we'll be happy to help! Thanks, Sous-Chefs.