1

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.

Shreyas B
  • 475
  • 2
  • 11
  • 2
    The typical approach is to bundle privileges in roles, so you just grant the role to a new user. As a user and a role is essentially the same thing, you can simply grant the existing user to the new user, thus the new user has the same privileges. –  Jan 17 '22 at 12:55

1 Answers1

1

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263