It is good to keep in mind that the createuser command in PostgreSQL is not a standard SQL query. Instead, it works as a wrapper for the CREATE ROLE statement in standard SQL.
Requirements:
This tutorial assumes that you have a PostgreSQL server installed on your machine. If you are using a remote cluster, ensure that you have sufficient permissions to create new roles on the server.
Verify the Access to the Createuser Command
Before running the createuser command, ensure that the binary in your system path is good. You can verify this by running the following command from your system shell:
This should return the version of the installed createuser utility. An example output is as follows:
PostgreSQL Create User Command
Once verified, you now have access to the createuser command which allows us to create new roles on the server.
The command syntax is as follows:
The command accepts various options to modify its behavior as outlined in the following:
- -s or –superuser – Creates a superuser account with all privileges.
- -U or –username – Specifies the username of the database superuser.
- -P or –pwprompt – Prompts for a password for the new user.
- -e or –echo – Displays the generated SQL command.
- -d or –createdb – Allows the new user to create databases.
- -D or –no-createdb – Disallows the new user from creating databases.
- -r or –createrole – Allows the new user to create roles.
- -R or –no-createrole – Disallows the new user from creating roles.
- -l or –login – Allows the new user to log in.
- -L or –no-login – Disallows the new user from logging in.
Let us cover the basic examples of using this command to create various user types.
Example 1: Create a User with a Password
To create a user with a password and the ability to create new databases, we can use the -p and -d options as demonstrated in the following:
The given command tells the createuser command to create a new user with the “sample_user” username. We also use the -U option to tell PostgreSQL that we wish to execute this command as the Postgres user.
Example 2: Create a User in Interactive Mode
The createuser command also allows us to create a new user in interactive mode using the –interactive option.
The given command creates a new user with the “new_user” username. Using the –interactive option allows the query to prompt you for various new user properties.
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y
Password:
We can also create a new user as a superuser and assign a password to the new user immediately.
The command is as follows:
Output:
Enter it again:
Password:
SELECT pg_catalog.set_config('search_path', '', false);
CREATE ROLE new_superuser PASSWORD 'SCRAM-SHA-256$4096:CHkcuoY6A+v4Pk2wNeaazQ==$J7RztLxuWrYDdtHqwNRyt0J4XvG2J5PuDHUdGxYkGWg=:z5+UkQiuvbEqSa/zrkTuiFZGmhSHwFkmvpVP0k4LJx4=' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;
This should set up a new user in the server and grant the superuser permissions to the new user.
Conclusion
We explored how we can use the createuser command in PostgreSQL to setup the new database users straight from the host shell.
