CODEWITHSUNDEEP
postgresqlpsql
Shreyas B
Shreyas B

Reputation: 505

PostgreSQL duplicate a user with same permission

I'm currently working on some project migrations and I want to create a single user in our New PostgreSQL with the same set of roles and permissions as the one in existing PostgreSQL.

I have searched regarding this but haven't found any answers.

how to create duplicate role of a user in postgres -> This solution is for same PostgreSQL instance

https://www.postgresonline.com/journal/archives/81-Backing-up-Login-Roles-aka-Users-and-Group-Roles.html -> Copies all the users with the same username and password. I don't want that.

Note: I don't want to copy the user ( username and password can be different, however, can be the same if the solution is easier), all I want is to create a new user in a new instance based on roles and permission in the existing PostgreSQL.

Upvotes: 1

Views: 2568

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247445

That is not easy to do, because permissions are not stored on the user, but on the object: every table, view, function etc. knows which user has which permissions on it. So to copy the permissions of a user, you'd have to copy all the object definitions.

So that is what you can do: run pg_dump -s dbname to dump all metadata of a database and restore them to the new database. If you want to change the user name in the new database, just rename the role.

Upvotes: 1

Related Questions