Unable to use Postgresql 11 as a storage backend

[ghost]

Describe the bug
Duplicate of #3949 vault will not start using a postgresql 11 backend. Both are clean install on a clean OS installation. The documentation does not detail what version of Postgresql (or any storage backend) are required or supported.

I do not know Go and as such cannot do a PR, but this is a bug and so the issue shouldn't get closed until resolved.

To Reproduce
Steps to reproduce the behavior:

1. Install and initialize Postgresql 11 server
2. Configure the backend and plugin per published Hashicopr documentation
3. Attempt to start the service
4. View error with systemctl status vault

Expected behavior
Ability to use Postgresql 11 as a storage backend.

I also expect the storage documentation to list what versions of the storage applications are working/supported.

Environment:

• Vault Server Version (retrieve with vault status): 1.1.2
• Vault CLI Version (retrieve with vault version): 1.1.2
• Server Operating System/Architecture: CentOS 7.6

Vault server configuration file(s):

Config is the example from the Vault deployment guide and Postgresql storage documentation.

Additional context
Documentation should have the information on supported versions for each storage backend as well to avoid this type of issue going forward.

[ncabatoff]

Hi @evitalis,

We have automated tests that exercise the Postgres storage backend. Currently they're using the 11.1 pg docker image, and they're passing. Also, I recently helped get HA support for the Postgres backend merged to master (not yet available in a release) and manually tested with pg 11 without any problems.

What is the error you're seeing?

[ghost]

@ncabatoff Error initializing storage of type postgresql: failed to check for native upsert when I start the service.

[ncabatoff]

That's the entire line? The line of code in question is

		return nil, errwrap.Wrapf("failed to check for native upsert: {{err}}", err)

where {{err}} would normally be replaced with the underlying error. The full context is

	// Determine if we should use an upsert function (versions < 9.5)
	var upsert_required bool
	upsert_required_query := "SELECT current_setting('server_version_num')::int < 90500"
	if err := db.QueryRow(upsert_required_query).Scan(&upsert_required); err != nil {
		return nil, errwrap.Wrapf("failed to check for native upsert: {{err}}", err)
	}

You might try running SELECT current_setting('server_version_num')::int < 90500 using the same user Vault is configured to run as and see what it returns. Here's what I see with a pg 11.1 docker:

psql (11.1 (Debian 11.1-1.pgdg90+1))
Type "help" for help.

postgres=# SELECT current_setting('server_version_num')::int < 90500
postgres-# ;
 ?column? 
----------
 f
(1 row)

[ghost]

@ncabatoff I did another test using 11.2 on Fedora 30 and it seemed fine. Not sure what happened before I opened this issue. I am going to close it and will open a new one if I run into the issue again.

Next time I should be able to have a copy of any output for you.