Re: Allow an user (not superuser) to create a testing database from a production database

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]


> On 07/06/2023 23:19 CEST Jorge Silva <jorge.silva93@xxxxxxxxx> wrote:
> We have a database which recurrently we create a testing database making a
> copy of it. Under the superuser “postgres” we use the command
> “CREATE DATABASE totvs_test WITH TEMPLATE totvs_production;” 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 for
me given that it's a production database.  But I don't know your requirements
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 same
> command.

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

	createdb totvs_test && pg_dump -Fc totvs_production | pg_restore -d totvs_test --single-transaction --verbose

> In order to achieve this, it seems that I have to give him the access
> “CREATEDB” and make our production database a template database.


> Do you know if there any safety, performance or any other kind of issue in
> making our production database as a template in postgres and keep using it
> 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 don't
know how that affects performance.

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

> 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 INSERT.

The docs[1] on templates databases don't say anything about access privileges
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 never
tested it with a user that doesn't have full access.



[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux