How to Use Postgres to Drop a User from a Database Cluster
Introduction
When you’re administering a CockroachDB database cluster, you may find yourself needing to drop a particular user from the cluster. It’s easy to remove a CockroachDB user with the DROP USER
command. In this article, we’ll take a closer look at the Postgres DROP USER command and check out some examples of its use.
Prerequisites
Before you proceed with this tutorial, make sure you have PostgreSQL installed and running, and confirm that you also have access to the psql
command-line interface for PostgreSQL. It’s also helpful to have some fundamental knowledge of database management systems in order to understand the examples in this article.
Open and access the command-line interface in PostgreSQL
In PostgreSQL, the user called postgres
has full superuser privileges and thus can access everything that resides in your PostgreSQL installation.
The following command will let you enter the psql
command-line interface as the postgres
superuser:
1 | $ sudo -u postgres psql |
Creating user in PostgreSQL for us to remove with the DROP USER command
Once you’re connected to psql
, you’ll be able to create a user with the WITH LOGIN
attribute. If you want to have a secured role, you can also add the ENCRYPTED PASSWORD
attribute to the CREATE USER
statement.
Here’s an example of the CREATE USER
command:
1 | CREATE USER newuser WITH LOGIN ENCRYPTED PASSWORD '1234'; |
To verify that your new user was created successfully, use the command \du
. You should receive output that looks like the following:
1 2 3 4 5 6 7 | List OF roles ROLE name | Attributes | Member OF --------------+------------------------------------------------------------+----------- newuser | | {} objectrocket | CREATE DB | {} orkb | | {} postgres | Superuser, CREATE ROLE, CREATE DB, Replication, Bypass RLS | {} |
Dropping a user in PostgreSQL
Only a superuser with the privileges to alter users, roles and groups can DROP
, or delete, another Postgres user.
The basic syntax for the DROP USER
SQL statement is:
1 | DROP USER [USERNAME]; |
NOTE: You can also drop a ROLE
in the same way that you would a USER
.
You can delete or remove the user that you created earlier in this tutorial, or you can delete any user on your system that you’re no longer using. We’ll use the following command to drop a user using the DROP USER
statement:
1 | DROP USER newuser; |
In this example, the DROP USER
command would drop the user called newuser in the PostgreSQL database.
To verify that the user was actually dropped, use the \du
command and you’ll see that newuser does not exist.
To exit the psql
interface, enter the command \q
in PostgreSQL.
PostgreSQL DROP USER CASCADE
If a user has privileges that have been granted to them, it can be only dropped by using the command DROP CASCADE
in the terminal.
Let’s look at an example:
1 | DROP USER newuser CASCADE; |
A user cannot be dropped if there is still a reference to it in any database of the cluster; you’ll receive an error if you attempt to delete it. You’ll need to drop all the objects on their own before you drop the user and remove the privileges the user has been granted on the other objects.
Drop a ROLE in Postgres
A Postgres “role” is very similar to a Postgres “user”, but a key distinction exists. While a role can be either a group or a user, roles that have login privileges are defined as “users”.
To drop a Postgres ROLE
, just use the DROP
command syntax in the same way that you would to delete a user:
1 | DROP ROLE newuser; |
Conclusion
Deleting a user in PostgreSQL is a permanent operation, so it’s important for database administrators to know how to perform the task correctly. In this article, we walked you through the process of dropping a user in PostgreSQL, and we provided examples of the Postgres DROP USER command to illustrate the process. With our examples to guide you, you’ll be ready to drop users from your own PostgreSQL database.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started