Linux delete postgresql user

How to Drop User from Postgres Database [duplicate]

I have the following postgres users which I can view by invoking the \du command in the terminal as follows:

postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication | <> tutorial1 | | <> 

According to the postgres documentation, I should be able to drop the user called «tutorial1» by entering the following command:

postgres=# DROP USER tutorial1 

However, when I use that command nothing happens. The documentation doesn’t provide any hints as to why this isn’t working, nor does it provide clear examples. That being said— what is the command to drop this user?

6 Answers 6

I’ve wasted way too much time on trying to find all the things to unwind. In addition to the above (or possibly as a complete replacement), refer to this answer to a similar question: https://stackoverflow.com/a/11750309/647581

DROP OWNED BY your_user; DROP USER your_user; 

This will drop (delete) all of the objects that your_user owns, right? We should probably edit this answer to include a big warning about that?

If you find yourself here (like me) because you are unable to drop the user, the following template may be helpful:

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM tutorial1; REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM tutorial1; REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM tutorial1; DROP USER tutorial1; 

The user may have privileges in other schemas, in which case you will have to run the appropriate REVOKE line with «public» replaced by the correct schema. To show all of the schemas and privilege types for a user, I edited the \dp command to make this query:

SELECT n.nspname as "Schema", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'foreign table' END as "Type" FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE pg_catalog.array_to_string(c.relacl, E'\n') LIKE '%postgres%'; 

I’m not sure which privilege types correspond to revoking on TABLES, SEQUENCES, or FUNCTIONS, but I think all of them fall under one of the three.

Источник

Linux delete postgresql user

dropuser — remove a PostgreSQL user account

Synopsis

dropuser [ connection-option . ] [ option . ] [ username ]

Description

dropuser removes an existing PostgreSQL user. Only superusers and users with the CREATEROLE privilege can remove PostgreSQL users. (To remove a superuser, you must yourself be a superuser.)

dropuser is a wrapper around the SQL command DROP ROLE . There is no effective difference between dropping users via this utility and via other methods for accessing the server.

Options

dropuser accepts the following command-line arguments:

Specifies the name of the PostgreSQL user to be removed. You will be prompted for a name if none is specified on the command line and the -i / —interactive option is used.

Echo the commands that dropuser generates and sends to the server.

Prompt for confirmation before actually removing the user, and prompt for the user name if none is specified on the command line.

Print the dropuser version and exit.

Do not throw an error if the user does not exist. A notice is issued in this case.

Show help about dropuser command line arguments, and exit.

dropuser also accepts the following command-line arguments for connection parameters:

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket.

Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections.

User name to connect as (not the user name to drop).

Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.

Force dropuser to prompt for a password before connecting to a database.

This option is never essential, since dropuser will automatically prompt for a password if the server demands password authentication. However, dropuser will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt.

Environment

Default connection parameters

Specifies whether to use color in diagnostic messages. Possible values are always , auto and never .

This utility, like most other PostgreSQL utilities, also uses the environment variables supported by libpq (see Section 34.15).

Diagnostics

In case of difficulty, see DROP ROLE and psql for discussions of potential problems and error messages. The database server must be running at the targeted host. Also, any default connection settings and environment variables used by the libpq front-end library will apply.

Examples

To remove user joe from the default database server:

To remove user joe using the server on host eden , port 5000, with verification and a peek at the underlying command:

$ dropuser -p 5000 -h eden -i -e joe Role "joe" will be permanently removed. Are you sure? (y/n) y DROP ROLE joe; 

See Also

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

Copyright © 1996-2023 The PostgreSQL Global Development Group

Источник

How to Delete a Postgres User (Drop User)

Administering a database requires removing user accounts, especially if a user account is no longer needed. Deleting an unnecessary Postgres users is an essential database security practice. This action also removes a potentially unnoticeable access point for hackers.

This tutorial explains how to drop a Postgres user in multiple ways.

• Access to the command line or terminal with sudo privileges.
• Postgres installed, preferably the latest version.
• Postgres users to drop. Follow our tutorial to create Postgres users for testing purposes.

Delete a Postgres User

A user in PostgreSQL has login privileges by default. There are two methods to remove a user, and both require access to the CREATEROLE rights. To drop a superuser, the SUPERUSER privilege is mandatory.

The postgres user created during installation has all the necessary privileges by default. The examples below use the postgres user to connect to PostgreSQL.

Method 1: Delete a Postgres User With dropuser Utility

Postgres offers a client utility for removing a user without connecting to the PSQL command-line interface.

To delete a user in Postgres with dropuser , run the following command in the terminal:

sudo -u postgres dropuser -e

Note: The -e tag echoes the server’s response, showing whether the command completes successfully. Without the tag, if the command is successful, the terminal does not output a message.

Alternatively, split the command into two steps:

1. Switch to the postgres user (or another user with the correct privileges):

2. Run the dropuser command:

In both cases, Postgres removes the user.

Method 2: Delete a Postgres User With DROP USER Statement

Another way to delete a user from a Postgres database is using a PSQL statement.

To delete a user, follow the steps below:

The terminal changes to the PSQL console ( postgres=# ).

2. List all users and roles with:

Locate the user for removal and use the name in the following step.

3. Run the following query to delete a user:

Alternatively, to check if a user exists before dropping, enter:

The client notifies if the role is nonexistent.

4. Display the user list again to confirm the user is gone:

The user is no longer on the list of roles. This output indicates a successful deletion.

Delete Multiple Postgres Users

The PSQL client allows deleting more than one Postgres user at once. The instructions below explain how to connect to the PostgreSQL client and delete multiple users.

1. Connect to the PSQL client with:

Locate the users for deletion and use the names in the following step.

3. Delete multiple Postgres users by separating individual users with a comma:

The command removes multiple roles at once. Add IF EXISTS to skip users that are not available.

4. Recheck the user list to confirm all the roles are deleted:

The removed users are no longer on the list.

Delete a Postgres User with Dependencies

Attempting to remove a Postgres user with dependencies fails and shows an error.

To safely remove the user, do the following:

1. Assign the object ownership from the error detail to another user. For example, to transfer the objects owned by myuser to postgres, run:

REASSIGN OWNED BY myuser TO postgres;

The query changes the object ownership to the postgres user.

2. Remove the database object connections to the user with:

This step also removes any privileges the user has over the object.

3. At the moment, the user no longer has any dependencies. To drop the user, run:

Since no dependencies exist, the removal is successful.

Delete a Postgres Role

To delete a Postgres role, run the following command in the PSQL client:

The DROP USER statement is an alias for DROP ROLE . The Postgres users are roles with LOGIN permissions. Therefore, both DROP USER and DROP ROLE are interchangeable and work for both users and roles.

After following the steps from this guide, you know how to delete a Postgres user and multiple users securely.

Milica Dancuk is a technical writer at phoenixNAP who is passionate about programming. Her background in Electrical Engineering and Computing combined with her teaching experience give her the ability to easily explain complex technical concepts through her content.

This tutorial shows three easy ways to create a database in PostgreSQL. Whether you prefer using a GUI or the.

User creation and management is an important aspect of database security. Learn how to create various user types in PostgreSQL in this guide.

Источник