Date: Thu, 8 Jun 2023 15:25:55 +0200 (CEST)
From: Erik Wienhold <ewie@ewie.name>
To: Jorge Silva <jorge.silva93@gmail.com>,
	"pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Message-ID: <1940899558.103110.1686230755873@office.mailbox.org>
In-Reply-To: 
 <CP6P284MB1930AE5210C0F7C566CEC980FE53A@CP6P284MB1930.BRAP284.PROD.OUTLOOK.COM>
References: 
 <CP6P284MB1930AE5210C0F7C566CEC980FE53A@CP6P284MB1930.BRAP284.PROD.OUTLOOK.COM>
Subject: Re: Allow an user (not superuser) to create a testing database from
 a production database
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Importance: Normal
Archived-At: 
 <https://www.postgresql.org/message-id/1940899558.103110.1686230755873%40office.mailbox.org>
Precedence: bulk

> On 07/06/2023 23:19 CEST Jorge Silva <jorge.silva93@gmail.com> wrote:
>
> We have a database which recurrently we create a testing database making =
a
> copy of it. Under the superuser =E2=80=9Cpostgres=E2=80=9D we use the com=
mand
> =E2=80=9CCREATE DATABASE totvs_test WITH TEMPLATE totvs_production;=E2=80=
=9D which works
> pretty well.

How do you deal with existing connections to totvs_production?  Connections=
 to
the template database are not possible during CREATE DATABASE and existing
connections must be closed beforehand.  That alone would be a dealbreaker f=
or
me given that it's a production database.  But I don't know your requiremen=
ts
on availability.

> But now I would like to allow an user, which is not the owner of the
> totvs_production database, and neither is a superuser, to execute this sa=
me
> command.

Is it a requirement to create the database via SQL?  The same can be achiev=
ed
on the command line with pg_dump and pg_restore which are designed for copy=
ing
databases without preventing other database connections.

=09createdb totvs_test && pg_dump -Fc totvs_production | pg_restore -d totv=
s_test --single-transaction --verbose

> In order to achieve this, it seems that I have to give him the access
> =E2=80=9CCREATEDB=E2=80=9D and make our production database a template da=
tabase.

Yes.

> Do you know if there any safety, performance or any other kind of issue i=
n
> making our production database as a template in postgres and keep using i=
t
> for production tasks?

Autovacuum uses a different default freeze age for template databases [0].
So the global vacuum setting does not apply to template databases.  But I d=
on't
know how that affects performance.

Also I would not expect a live database to be flagged as a template databas=
e.

> This user, who would make this copy, already has access to SELECT in all =
the
> data of this database, but they have no rights in DELETE, UPDATE or INSER=
T.

The docs[1] on templates databases don't say anything about access privileg=
es
besides the CREATEDB and SUPERUSER privileges that you already mentioned.  =
You
have to test that and see if the new database contains everything.

The access privileges definitely affect what the user can dump with pg_dump=
.
That's why pg_dump is usually run as superuser to make a full copy.  I neve=
r
tested it with a user that doesn't have full access.

[0] https://git.postgresql.org/gitweb/?p=3Dpostgresql.git;a=3Dblob;f=3Dsrc/=
backend/postmaster/autovacuum.c;h=3Df929b62e8adaa4219694c2c74648c623d7e9f68=
8;hb=3D378d73ef204d0dcbeab834d52478e8cb90578ab7#l2054
[1] https://www.postgresql.org/docs/current/manage-ag-templatedbs.html

--
Erik