PostgreSQL – DROP ROLE
In PostgreSQL the DROP ROLE statement to remove a role.
Syntax: DROP ROLE [IF EXISTS] target_role;
Let’s analyze the above syntax:
- After the DROP ROLE keywords add the role name that is to be deleted.
- Use the IF EXISTS option can be used to issue a notice instead of an error when a request to remove a role that does not exist is made.
it is important to note that to remove a superuser role, one needs to be a superuser. To remove a non-superuser roles, you will need the privilege to create a role using the CREATE ROLE statement. If we accidentally try to remove a role that has been referenced in any database, PostgreSQL raises an error. In this case, one has to take two steps:
- You either need to remove the database objects owned by that particular role using the DROP OWNED statement or reassign the ownership of the database objects to another role using the REASSIGN OWNED statement.
- You will also be needing to revoke all other permissions granted to the role.
In other words, follow the below statements exactly as shown to drop a role:
REASSIGN OWNED BY target_role TO another_role; DROP OWNED BY target_role; DROP ROLE target_role;
Example:
In this example:
- Here, we will create a new role called w3wiki and use this role to create a table named employees.
- Then, we will show the step by step guide on how to remove the role w3wiki from the PostgreSQL database server.
First, log in to PostgreSQL using the Postgres role:
psql -U postgres
Second, create a new role called w3wiki using the below statement:
create role w3wiki with login password 'Beginner12345';
Third, grant createdb privilege to w3wiki using the below statements:
alter role w3wiki createdb;
Fourth, create a new database called sales:
create database sales;
Now, login to the PostgreSQL database server using the w3wiki role as shown below:
psql -U w3wiki -W sales
Add a new table to the Sales database:
create table employee( customer_id int generated always as identity, customer_name varchar(150) not null, primary key(employee_id) );
Now show the list of tables in the sales database using the below command:
\du
This will show you the following result:
Now log in as Postgres and attempt to drop the w3wiki role:
drop role w3wiki;
Output:
Contact Us