Topic: How to Drop User or Role in PostgreSQL Instance on Google Cloud Platform
How to Drop User or Role in PostgreSQL Instance on Google Cloud Platform | GCP SQL Tutorial 2022, in this video we are going to learn How to Drop User or Role in PostgreSQL Instance on Google Cloud Platform | GCP SQL Tutorial 2022
Script:
ERROR: role "aamir" cannot be dropped because some objects depend on it
DETAIL: owner of database test
1 object in database test
SQL state: 2BP01
Error
Invalid request: failed to delete user aamir: . role "aamir" cannot be dropped because some objects depend on it Details: owner of database test 1 object in database test.
0) Prepare the scenario
Create user aamir and then create some objects
create database Test
create table public.mytable(id int, name varchar(100));
insert into public.mytable values(1,'aamir');
Select * from public.mytable;
1) -- Let's think about a user who has left the company and you need to drop the user. If you don't know the
--password for the user, login by using postgres user and change the password of user.
--How to change the password for role
Alter role aamir LOGIN password 'Test123$';
2) -- Tried to drop the role by using postgres user session
drop role aamir -- you will get errot that objects are owened by user aamir
GRANT postgres to aamir;
--2 -- login by aamir user and run below command to assign all objects to postgres user
REASSIGN OWNED BY aamir TO postgres;
--3 login back to postgres and run below
drop role aamir
--4 Check if Objects are not dropped by dropping user.
To remove a user or role from PostgreSQL, use the DROP ROLE or DROP USER commands. This deletes the user or role from the database, as well as any related rights. Dropping users and roles should be done with caution because this action cannot be reversed. https://www.helpwithdissertation.co.uk/research-topics/google-scholar-research-topics/
ReplyDelete