2

I was learning PostgreSQL like a year ago and started to fill information on a database that the Postgres user was the owner. This data is a lot, and now I want to move it to production but as is known this is a very bad practice, so I created another user and ran the command

ALTER DATABASE modelos_vehiculos OWNER TO new_user;

To transfer the ownership to the new user. The response on psql was

ALTER DATABASE

But now when I try to log with the new user, it says that the user have no permission. I saw on pgadmin and the new user is the owner, but this can't do anything on the database neither SELECT, INSERT,...

How can I concede all the permissions to this user?

I also saw that the correct way to do this is with this command instead of the ALTER one

REASSIGN OWNED BY old_name TO new_name;

But this command can't be used when the owner is the Postgres user because It will transfer all the databases to the user, including the databases used by Postgres to operate.

Ken White
  • 123,280
  • 14
  • 225
  • 444
TTT2
  • 549
  • 2
  • 13
  • might be easier to just rename your previous owner to the new name, rather than "transferring" everything over. you can then create a new user under your existing name. – Sam Mason Dec 14 '22 at 21:43
  • but the previous owner is the super user postgres, rename it isn't bad? – TTT2 Dec 14 '22 at 22:26
  • 1
    Oops, sorry didn't realize you meant your current user was the superuser. Not sure how I misinterpreted that! – Sam Mason Dec 14 '22 at 22:41
  • @SamMason Thanks for the reply but not, if you do that you will transfer all the databases to the user you specify including those databases that postgres uses to correctly works like the `postgres` one or the `template0` `template1` , remember the database owner in my case is the super user postgres – TTT2 Dec 15 '22 at 01:32
  • nvm just read the second answer and worked, thanks you a lot – TTT2 Dec 15 '22 at 01:40
  • 1
    Yup there seemed to be lots of different suggestions on that question, I thought one of them could be turned into something relevant to your problem! – Sam Mason Dec 15 '22 at 09:10

0 Answers0