This article describes how to add and delete PostgreSQL databases and users from the command line.
A default PostgresSQL installation always includes the postgres superuser. Initially, you must connect to PostgreSQL as the postgres user until you create other users (which are also referred to as roles).
To create a PostgreSQL user, follow these steps:
su - postgres
You can now run commands as the PostgreSQL superuser. To create a user, type the following command:
At the Enter name of role to add: prompt, type the user's name.
At the Enter password for new role: prompt, type a password for the user.
At the Enter it again: prompt, retype the password.
At the Shall the new role be a superuser? prompt, type y if you want to grant superuser access. Otherwise, type n.
At the Shall the new role be allowed to create databases? prompt, type y if you want to allow the user to create new databases. Otherwise, type n.
At the Shall the new role be allowed to create more new roles? prompt, type y if you want to allow the user to create new users. Otherwise, type n.
PostgreSQL creates the user with the settings you specified.
To create a PostgreSQL database, follow these steps:
su - postgres
You can now run commands as the PostgreSQL superuser. To create a database, type the following command. Replace user with the name of the user that you want to own the database, and replace dbname with the name of the database that you want to create:
createdb -O user dbname
To grant an existing user privileges to a database, follow these steps:
GRANT permissions ON DATABASE dbname TO username;
For detailed information about the access privileges that are available in PostgreSQL, please visit http://www.postgresql.org/docs/9.1/static/sql-grant.html.
The user can now access the database with the specified permissions.
Similar to the createdb command for creating databases, there is the dropdb command for deleting databases. To delete a database, you must be the owner or have superuser privileges.
Type the following command, replacing dbname with the name of the database that you want to delete:
The dropdb program does not ask for confirmation before deleting a database. As soon as you press Enter, PostgreSQL deletes the database and all of the data it contains.
Similar to the createuser command for creating users, there is the dropuser command for deleting users.
To delete a specific user, type the following command. Replace username with the name of the user that you want to delete:
If the user owns any databases or other objects, you cannot drop the user. Instead, you receive an error message similar to the following:
dropuser: removal of role "username" failed: ERROR: role "username" cannot be dropped because some objects depend on it DETAIL: owner of database dbname
You should change the database's owner (or drop the database entirely), and then you can drop the user.
To view the official PostgreSQL documentation, please visit http://www.postgresql.org/docs.