Re: [pmg-devel] [PATCH pmg-api 1/3] dbtools: grant permissions public schema for created databases

[Stoiko Ivanov <s.ivanov@proxmox.com>]

Thanks for the patch!

On Fri, 23 Jun 2023 14:21:00 +0200
Dominik Csapak <d.csapak@proxmox.com> wrote:

> since postgres 15, the public schema is not world writeable anymore for
> security reasons. In our environment, where the db is not externaly
> reachable and no database users should exists except the ones we create,
> we can safely give the permissions again to be able to use
> the root/www-data user without modification of the remaining
> code/privileges for postgres.
> 
> Signed-off-by: Dominik Csapak <d.csapak@proxmox.com>
> ---
>  src/PMG/DBTools.pm | 8 ++++++++
>  1 file changed, 8 insertions(+)
> 
> diff --git a/src/PMG/DBTools.pm b/src/PMG/DBTools.pm
> index 0b37361..f8eb054 100644
> --- a/src/PMG/DBTools.pm
> +++ b/src/PMG/DBTools.pm
> @@ -344,6 +344,14 @@ my $createdb = sub {
>  	'--lc-ctype=C',
>  	$dbname,
>      );
> +
> +    # allow root and www-data to access the public SCHEMA like pre prostgres15
> +    # this is not a security issue, since the db is not externally reachable anyway and no
> +    # other users should exist
> +    my $cmd = "GRANT CREATE ON SCHEMA public To \"root\";"
> +	."GRANT USAGE ON SCHEMA public To \"root\";"
> +	."GRANT CREATE ON SCHEMA public To \"www-data\";"
> +	."GRANT USAGE ON SCHEMA public To \"www-data\";";
>  };

the command is placed in a variable, but never actually called?
looking through DBTools - it might be a better fit to declare
a sub create_user, and put the `createuser` invocation as well as the
GRANT sql commands there - but the separate sub is only a suggestion.


>  
>  sub create_ruledb {