How to Change the Table Owner in PostgreSQL

PostgreSQL supports different users with different roles. As the admin, you can create other users and allow them to access the database using a password. Each user can have defined roles that restrict what they can do in the database. When you create a PostgreSQL table, you become the owner, but this ownership can quickly get transferred to another user.

With the “ALTER TABLE” PostgreSQL command, it’s possible to change the table owner. This post covers the detailed steps to follow and provides an example on how to change the table owner in PostgreSQL.

Step-by-Step Guide to Changing the Table Owner in PostgreSQL

Changing a table ownership is a straightforward task. The following are the key steps that you should follow:

Step 1: Check Table Owner
You can have numerous tables in your database. Before changing the table ownership, you should check who the current owner is. For that, we use the “\dt” command.

The previous image shows the table details including the owner. If you only want to get the details of a specific table, specify its name with the same command.

We now get the specific details for the “customers” table. From the output, we see that its owner is Postgres.

Now that we know the current owner of our target table, we can proceed to see how to change the ownership.

Step 2: Create a User
You can only change table ownership if you have an existing user. However, it’s possible to quickly create a new PostgreSQL user for your database and give them the table ownership. To create a new user, we use the following command:

We create a user named “owner1”. If you already have an existing user, you can skip this section.

After creating the user, access the list of available users to ascertain that your user exists. Use the following command to view all the users:

We confirm that our “owner1” user exists in our database. From here, we can now transfer the table ownership.

Step 3: Change the Table Owner in PostgreSQL
Any modifications in a table, including changing the owner, are done using the “ALTER TABLE” command. For this case, we use the following syntax:

For our case, we saw that the current user is “postgres” and we want to make “owner1” as the new table owner. Our target table is “customers”. Therefore, we run our command as follows:

Once you get a similar output, it confirms that the change of table owner is a success. The remaining thing is to verify that the change of table ownership is successful. For that, we must recheck the table details as we did earlier using the following command:

From the following output, we confirm that the “customers” table is now owned by “owner1”. That’s how you can quickly change the table owner in PostgreSQL with the simple steps that we covered.

Feel free to alter your table per your needs and change the ownership to meet your goal.

Conclusion

There are different reasons why you may want to change the table owner in PostgreSQL. Whatever the reasons are, the process is simple. First, identify the current owner of the table. Next, create a new user or check the existing users to determine which user you wish to take the new ownership. From there, run the “ALTER TABLE” command to change the table ownership. Lastly, verify that the table ownership has changed. All the steps are detailed in this post.