[PATCH consfigurator v3] Add some properties to install and configure Postgresql

[Sean Whitton]

Hello David,

On Wed 29 Dec 2021 at 02:01PM -04, David Bremner wrote:

> diff --git a/src/property/postgres.lisp b/src/property/postgres.lisp
> new file mode 100644
> index 0000000..f50ce6b
> --- /dev/null
> +++ b/src/property/postgres.lisp
> +(defproplist has-role :posix (role)
> +  "Ensure ROLE exists in the Postgres cluster."
> +  (:desc #?"Postgres role ${role} exists")
> +  (installed)
> +  (%run-sql
> +   #?"DO $$
> +      BEGIN
> +      CREATE ROLE ${role};
> +      EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping',
> +        SQLERRM USING ERRCODE = SQLSTATE;
> +      END
> +      $$;"))
> +
> +(defproplist has-database :posix (db-name)
> +  "Ensure Postgres DATABASE exists"
> +  (:desc #?"Postgres database ${db-name} exists")
> +  (installed)
> +  ;; this has a potential race condition between test and creation
> +  (%run-sql
> +   (strcat
> +    #?"SELECT 'CREATE DATABASE ${db-name}' WHERE NOT EXISTS "
> +    #?"(SELECT FROM pg_database WHERE datname = '${db-name}')\\gexec")))
> +
> +(defproplist has-owner :posix (database owner)
> +  (:desc #?"Postgres database ${database} has owner ${owner}")
> +  (installed)
> +  (%run-sql #?"ALTER DATABASE ${database} OWNER TO ${owner}"))
> +
> +(defproplist has-group :posix (user group)
> +  "Ensure Postgres user USER is a member of GROUP."
> +  (:desc #?"Postgres role ${user} is a member of group ${group}")
> +  (installed)
> +  (%run-sql #?"ALTER GROUP ${group} ADD USER ${user}"))
> +
> +(defproplist can-login :posix (user)
> +  "Ensure USER can login to Postgres."
> +  (:desc #?"Postgres role ${user} can login to database")
> +  (installed)
> +  (%run-sql #?"ALTER USER ${user} WITH LOGIN;"))

I'm sorry I didn't think of this earlier, but it now occurs to me that
all of these properties will report that a change was made every time
they are applied.  Can you think of some way to have them return
:NO-CHANGE when the sql does nothing?  Perhaps %RUN-SQL takes a string
to look for in the output.

If there is not an obvious way to do this then I am happy to apply the
patch as-is, but I thought I'd raise the issue for discussion, at least.

Thanks for the resubmission!

-- 
Sean Whitton