How to Use the Postgres CREATE USER Command
If you’re administering a PostgreSQL database, you’ll need to know how to add new users to the database as needed. Fortunately, it’s easy to add users with the help of the Postgres CREATE USER command. This command allows database administrators to create new users and grant passwords and privileges to these users as well. In this article, we’ll discuss the Postgres CREATE USER command and review the various options that can be used in conjunction with the command.
Before attempting to follow along with this article, make sure that PostgreSQL is installed and configured on your machine. If you’re running a Linux distribution that uses the
systemd software suite, you can use the following command to start a PostgreSQL database server:
sudo systemctl start postgresql
You can then verify that the process is running in the background with this command:
systemctl status postgresql
Connect to the PostgreSQL database as the superuser
In order to perform administrator commands such as creating a new user, we’ll need to log in to the PostgreSQL database cluster using the default superuser role. We can access the
psql command-line interface and elevate our privileges to those of the
postgres user with the following command:
sudo -u postgres psql
The PostgreSQL CREATE USER command
The PostgreSQL CREATE USER command defines and adds a new user in a PostgreSQL database. The main role of a user in a PostgreSQL database is to assign certain privileges to objects residing in the database.
Let’s look at the basic syntax for creating a user in Postgres:
CREATE USER user_name WITH [OPTION];
There are several important
options that can be used with this command:
UID– This clause can be used to define the UID of a new user in PostgreSQL. While it’s not usually required to do this, specifying a UID can be valuable if you’re recreating the owner for a particular orphaned object.
CREATEDB or NOCREATEDB– If the
CREATEDBoption is specified, the newly-created user will be allowed to create their own databases. If
NOCREATEDBis specified, the user will be denied the ability to create their own database. The default value for this option is
CREATEUSER or NOCREATEUSER– If the
CREATEUSERoption is specified, the new user will also be allowed to create another user. This option also makes the new user a superuser that can circumvent access restrictions. When no value is specified for this option, the default is
groupname– This option defines the new user as a member of an existing group.
password– This option will set a password for the user. If you don’t plan on using password authentication in your database, you can omit this option; however, the new user will not be able to log in or connect to a database if you switch to password authentication in the future.
abstime– This option specifies the time at which a user’s password is no longer valid.
In the following example, we’ll create a simple user:
CREATE USER someuser;
Next, we’ll create another user. Notice that this user is created with a defined password:
CREATE USER newuser WITH PASSWORD '12345';
Our next example specifies the absolute time for the validity of the user’s password:
CREATE USER user1 WITH PASSWORD VALID UNTIL '2020-12-31';
In our final example, we’ll create a new user with the privilege to create a database:
CREATE USER user2 WITH PASSWORD 'pass' CREATEDB;
NOTE: You can also use the
CREATE ROLE command as an alternative to the
Creating new users is an essential component of database administration. The Postgres CREATE USER command is used to create new users in a PostgreSQL database. In this article, we showed you how to use all the various options available with the
CREATE USER command and provided examples to illustrate the process. With these instructions and examples, you’ll be prepared to create new users in your own PostgreSQL environment.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started