How to Change Owner of a View in PostgreSQL Database Server

By Posted on

The need for changing the owner of a view in PostgreSQL Database Server

This article has a specific title. The title is actually a simple task for a database administrator. The task is about how to change the owner of a view in PostgreSQL Database Server. Every view in the PostgreSQL Database Server has a specific owner. Because of the specific ownership, there can be a problem whenever an inappropriate user accessing the view. So, the following article is suitable to answer the problem arise on accessing a view using a specific user. The following are steps for accomplishing the task :

The step for changing the owner of a view in PostgreSQL Database Server

1. First of all, make sure that the PostgreSQL Database Server is running.

The information is available in this site. It is available in article titled ‘Check PostgreSQL Service Status’ in this link. But the process for checking the PostgreSQL service status is for the local process. There is a different way to check a PostgreSQL Database Server running in a remote server.

2. Second, make sure that accessing the PostgreSQL Database Server running is possible. Specifically, using a specific user from the specific machine if it is a remote machine. Accessing the process is possible by executing the following command pattern : 

psql -Uusername databasename

For an example : 

user@hostname:~$ psql -Upostgres mydb
psql (10.5)
Type "help" for help.
mydb=>

3. Next, list all of the available views in the database. There is a query or to be exact a specific command to list all the available views in the database. The following is the command pattern : 

\d

For an example : 

mydb=> \d
                         List of relations
 Schema |                Name                |   Type   |  Owner   
--------+------------------------------------+----------+----------
...
...
...
 public | users                              | table    | pgsql
 public | users_id_seq                       | sequence | pgsql
 public | view_xxxxxxx                       | view     | pgsql
 public | view_xxxxxxx                       | view     | pgsql
(44 rows)
mydb=>

4. Finally, execute the command for changing the owner of the view. The following is the command pattern : 

alter view view_name owner to user_name;

Below is the example of the above command pattern usage : 

mydb=> alter view view_xxxxxxx owner to postgres;
ALTER TABLE
mydb=>

5. Last but not least, recheck the final owner of the view. The command pattern is available below : 

\d

Execute it as follows : 

mydb=> \d
                         List of relations
 Schema |                Name                |   Type   |  Owner   
--------+------------------------------------+----------+----------
...
...
...
 public | users                              | table    | pgsql
 public | users_id_seq                       | sequence | pgsql
 public | view_xxxxxxx                       | view     | postgres
 public | view_xxxxxxx                       | view     | pgsql
(44 rows)
mydb=>

Using the output of the above command. It is clearly seen that the owner of view_xxxxxx has changed from ‘pgsql’ to ‘postgres’. It means, the command execution is a success.

Leave a Reply