How to Use the Postgres CREATE USER Command

Introduction

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.

Prerequisites

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:

1
sudo systemctl start postgresql

You can then verify that the process is running in the background with this command:

1
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:

1
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:

1
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 CREATEDB option is specified, the newly-created user will be allowed to create their own databases. If NOCREATEDB is specified, the user will be denied the ability to create their own database. The default value for this option is NOCREATEDB.

  • CREATEUSER or NOCREATEUSER – If the CREATEUSER option 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 NOCREATEUSER.

  • 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:

1
CREATE USER someuser;

Next, we’ll create another user. Notice that this user is created with a defined password:

1
CREATE USER newuser WITH PASSWORD '12345';

Our next example specifies the absolute time for the validity of the user’s password:

1
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:

1
CREATE USER user2 WITH PASSWORD 'pass' CREATEDB;

NOTE: You can also use the CREATE ROLE command as an alternative to the CREATEUSER option.

Conclusion

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

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.