Drop user in PostgreSQL

Drop user means is easy process in PostgreSQL. But, if the user owns database or its objects(table, views, stored procedure, …) it troughs error. To drop user, we must have superuser privilege. or createrole privilege.

Syntax:

DROP USER user_name;

Example:

postgres=# drop user federer; DROP ROLE

But, when user owns database or database objects(tables, views, stored procedure,…) it through following error.

bank=# drop user elchur; ERROR: role "elchur" cannot be dropped because some objects depend on it DETAIL: owner of database xyz privileges for table accounts

Solution: Reassign ownership to other users or restrict the user to login into database server.

1. Reassign ownership to other user.

REASSIGN OWNED BY elchur TO anil;

With the above command, whatever user ‘elchur’ is having permissions are assigned to user ‘anil’. After the above command, we can drop the login.

2. The easiest solution for this scenario is disallow the user from connecting.

alter user elchur no login;

After running above command, try to login. Then, you will receive error like below.

psql -U elchur postgres Password for user elchur: psql: FATAL: role "elchur" is not permitted to log in

Related Posts

CheapSexCams
Responses are currently closed, but you can trackback from your own site.

Comments are closed.

Powered by k2schools
%d bloggers like this: