How to handle roles to dump and restore a PostgreSQL database on another database in the same cluster

Question

I am struggling with restoring a dump for several reasons and I am not even sure I am taking the right direction. Basically my issue seems to depends on my understanding of role and priviledges in postgre so feel free to teach me.

My goal is to create a copy my database on another database in the same cluster via my App. This is a legacy feature that previously used a superuser on the database cluster.

Now I am not sure how to proceed, I need to connect to original database, drop the target database and create it, then I execute this query :

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname='{target_database}';

and after that i generate a .bat file that I then execute. This file contains this command which is the main one :

pg_dump.exe -T dos_histomemo "postgresql://{username}:{encoded_pwd}@{ip}:{port}/{original_database}" | "{path_to_psql}psql.exe" -v ON_ERROR_STOP=1 "postgresql://{username}:{encoded_pwd}@{ip}:{port}/{target_database}"

what my tests have led to is that :

the user creating the target database owns it so it is easier if the one restoring it and creating are the same user.
the owner of the schema public is postgres so I will always have troubles because if I dump with --no-owner then i need to grant to my user_app permissions to create in schema on target_database and if I dumb with ownerships I need to resotre with postgres
When doing all of this with postgres I cannot restore roles because the ALTER DEFAULT PRIVILEDGES was used with others users

My question is: I am going the right direction and should I use postgres user to administrate every priviledges in the cluster ? Or more generaly, how to handle roles in this situation ? At the end I have to respect ISO 27001 norms for database users and I am not sure what it implies in this case.

This is a legacy feature that previously used a superuser on the database cluster. I don't think I'm following you. Are you trying to get App to do all this without a) being a superuser or b) without using CREATEDB as part of CREATE ROLE ...? Have you read carefully all the documentation for pg_dump and pg_restore (there are a lot of options)? It seems to me like everything you're trying to do could be accomplished using various ALTER... statements, but I'm not sure what your operational constraints are. — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Mar 24 at 12:25
ok maybe it not clear enough, I need to do the full process through my app, the less steps outisde of it the better i guess. Right now, I only have access to the user_app credentials (there are in my connection string). Since the app used to have a superuser postgres I tried sticking more or less to the same process since as you said there are a lot of options for pg_dump and pg_restore. My main concerns was that I cannot pipe pg_dump into pg_restore and it implies to potentially let a dump with all the customer data on the server. — imerle
– imerle, Commented Mar 24 at 12:36

Laurenz Albe · Accepted Answer · 2025-03-24 12:32:35Z

0

I would use the --clean option with pg_dump, so that the restore drops all conflicting objects.

Using a superuser to restore a dump is a good idea, and you don't need to worry that ALTER DEFAULT PRIVILEGES cannot be restored.

answered Mar 24 at 12:32

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

imerle Mar 24 at 13:35

Ok you just made me realise default priviledges are not useful to me in my use case, but only because it my feature create a sandbox database that don't need to extend privledges. But in the general use case, I'd like to be able to keep my default priviledges.

Laurenz Albe Mar 24 at 14:15

You will be able to keep them.

Collectives™ on Stack Overflow

How to handle roles to dump and restore a PostgreSQL database on another database in the same cluster

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related