How to Change the owner of a schema in PostgreSQL

By Posted on

Change the owner of a schema in PostgreSQL

This article will show how to change the owner of a schema in PostgreSQL database server. Basically, it is one way to be able to solve the permission problem. There are some certain conditions where the applications accessing the PostgreSQL database in a specific schema end in failure. One of the cause is because the user used in the applications for accessing the resource in a specific schema in the PostgreSQL database does not have enough permission. Generally, it triggers a permission denied error message. Beside granting specific privileges of the schema to the user, changing the owner of the schema can be a solution. The following are steps to achieve it :

  1. First of all, access the PostgreSQL command console. It exist by executing the following command : 

    [root@host ~]# psql -Upostgres -d db_app
psql (11.10)
Type "help" for help.

db_app=#

  2. Next, just list available schema in the database by typing the query ‘\dn’ as follows : 

    db_app=# \dn
   List of schemas
   Name        |  Owner
---------------+----------
 customer      | postgres
 distributor   | postgres
 product       | postgres
 transaction   | postgres
 public        | postgres
 vendor        | postgres
(6 rows)

db_app=#

  3. Moreover, before changing the owner of the schema from the available schema above, just list the user. Execute the following command to list the user : 

    db_app=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 admin     |                                                            | {}
 centos    | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}


db_app=#

  4. Finally, alter the owner of the schema accordingly. The pattern for altering the owner of the schema exist as follows : 

    alter schema schema_name owner to user_name

    The following is th execution of the command according to the above pattern : 

    db_app=# alter schema customer owner to admin;
ALTER SCHEMA
sak_djpk=#

  5. Do not forget to list the schema again to check the current ownership after executing the above command as follows : 

    db_app=# \dn
   List of schemas
   Name        |  Owner
---------------+----------
 customer      | admin
 distributor   | postgres
 product       | postgres
 transaction   | postgres
 public        | postgres
 vendor        | postgres
(6 rows)

db_app=#

    As in the above output, the ownership of schema ‘customer’ is no longer the same. Because of the alter query before, it changed from ‘postgres’ to ‘admin’.

Leave a Reply