Allow define a search_path on the database

[NitriKx]

In this PR I've added a search_path parameter on the postgresql_database resource, which allows to alter the database to set a value for its search path.

It's inspired from what has been done by on the role (the readSearchPath function has been moved in helpers.go as I've re-used it.

Web documentation has been updated as well 🙂

[cyrilgdn]

TIL pg_options_to_table 👀

[cyrilgdn]

Suggested change

	dbSQL := `SELECT setconfig FROM pg_catalog.pg_database AS d, pg_catalog.pg_db_role_setting AS drs WHERE d.datname = $1 AND d.oid = drs.setdatabase`
	dbSQL := `SELECT setconfig FROM pg_catalog.pg_database AS d, pg_catalog.pg_db_role_setting AS drs WHERE d.datname = $1 AND d.oid = drs.setdatabase AND setrole = 0`

I think you need to filter on setrole = 0 too, otherwise if someone do ALTER ROLE x IN DATABASE y SET search_path = 'test' on this database, this query will have multiple results.

e.g.:

postgres=# create database test; create role test;
CREATE DATABASE
CREATE ROLE

postgres=# alter database test set search_path = 'test'; alter role test in database test set search_path = 'other';
ALTER DATABASE
ALTER ROLE

postgres=# SELECT drs.* FROM pg_catalog.pg_database AS d, pg_catalog.pg_db_role_setting AS drs  WHERE d.datname = 'test' AND d.oid = drs.setdatabase;
 setdatabase | setrole |      setconfig      
-------------+---------+---------------------
       16384 |       0 | {search_path=test}
       16384 |   16385 | {search_path=other}
(2 rows)

This will probably work anyway as readSearchPath will take the first which is most probably the good one but I think the ordering isn't guarantee

[cyrilgdn]

As I discovered pg_options_to_table thanks to you, we should probably use it here too.

If you don't have time I can do it in another PR though

[cyrilgdn]

@NitriKx Thanks for your work on that 🙏

[NitriKx]

Thanks for your feedback @cyrilgdn, will try to do the changes (and rebase) next week 🙂

[teru01]

@NitriKx
Hi. Do you have any progress on it?
I can inherit this PR if you don't have enough time because I need the search_path feature as well.

[zam6ak]

@NitriKx CC @cyrilgdn
Being able to set search_path on DB level is really useful.
Any chance to get this merged?

[NitriKx]

Heey will try to work on this next week sorry