Postgresql how to create user

Creating user, database and adding access on PostgreSQL

NOTE : Right off the bat — this is valid as on March 2017, running on Ubuntu 16.04.2, with PostgreSQL 9.6

One nice thing about PGSQL is it comes with some utility binaries like createuser and createdb. So we will be making use of that.

As the default configuration of Postgres is, a user called postgres is made on and the user postgres has full superadmin access to entire PostgreSQL instance running on your OS.

The above command gets you the psql command line interface in full admin mode.

In the following commands, keep in mind the are to denote variables you have to set yourself. In the actual command, omit the <>

Creating user

Creating Database

Giving the user a password

Granting privileges on database

Doing purely via psql

Your OS might not have the createuser or createdb binaries, or you may, for some reason want to do it purely via psql, then these are the three magic commands —

Obligatory shameless self-plug :
I am one of the co-founders of Coding Blocks — A Software Programming bootcamp, based out of New Delhi, India. Among other things, we teach Full Stack Web Development using NodeJS, via both classroom programmes, as well as online classes. You can follow our Medium to find more articles on Android and Web development.

Postgresql how to create user

createuser — define a new PostgreSQL user account

Synopsis

Description

createuser creates a new PostgreSQL user (or more precisely, a role). Only superusers and users with CREATEROLE privilege can create new users, so createuser must be invoked by someone who can connect as a superuser or a user with CREATEROLE privilege.

If you wish to create a new superuser, you must connect as a superuser, not merely with CREATEROLE privilege. Being a superuser implies the ability to bypass all access permission checks within the database, so superuser access should not be granted lightly.

Options

createuser accepts the following command-line arguments:

Specifies the name of the PostgreSQL user to be created. This name must be different from all existing roles in this PostgreSQL installation.

-c number
—connection-limit= number

Set a maximum number of connections for the new user. The default is to set no limit.

The new user will be allowed to create databases.

The new user will not be allowed to create databases. This is the default.

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

This option is obsolete but still accepted for backward compatibility.

The new role will automatically inherit privileges of roles it is a member of. This is the default.

The new role will not automatically inherit privileges of roles it is a member of.

The new user will be allowed to log in (that is, the user name can be used as the initial session user identifier). This is the default.

The new user will not be allowed to log in. (A role without login privilege is still useful as a means of managing database permissions.)

If given, createuser will issue a prompt for the password of the new user. This is not necessary if you do not plan on using password authentication.

The new user will be allowed to create new roles (that is, this user will have CREATEROLE privilege).

The new user will not be allowed to create new roles. This is the default.

The new user will be a superuser.

The new user will not be a superuser. This is the default.

Print the createuser version and exit.

Show help about createuser command line arguments, and exit.

createuser 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.

-U username
—username= username

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

Force createuser to prompt for a password (for connecting to the server, not for the password of the new user).

Environment

Default connection parameters

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 CREATE 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 create a user joe on the default database server:

To create a user joe on the default database server with prompting for some additional attributes:

To create the user joe as a superuser, and assign a password immediately:

In the above example, the new password isn’t actually echoed when typed, but we show what was typed for clarity. As you see, the password is encrypted before it is sent to the client.

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-2022 The PostgreSQL Global Development Group

How to create a PostgreSQL database and users using psql and pgAdmin

SUMMARY: This article covers the steps for creating new databases and users in PostgreSQL using both psql and pgAdmin:

a. Creating a user with psql

b. Creating a database with psql

2. Using pgAdmin

a. Creating a user with pgAdmin
b. Creating a database with pgAdmin

While working with PostgreSQL the two basic requirements is to create a database and set up a few users. This will help us in eliminating the need for reinstallation, if we mess up the default set of databases or users that already exist, while trying to learn and build our understanding. We will go through the steps for both psql and pgAdmin in order to ensure that you do not get stuck if you are using one or the other.

Using psql

Creating a user with psql

In PostgreSQL, a user can easily be created using the CREATE USER command :

The reason the command success is returned as CREATE ROLE is that Postgres does not differentiate between a role and a user. The terms can be used interchangeably. If you are interested in learning more, you can read the documentation at the following link: https://www.postgresql.org/docs/12/sql-createrole.html.

There are a few useful options that can be used while creating the user. Let’s begin with the formatting for the command:

CREATE USER: This is the SQL syntax that is used to initiate the request for creating the user.

: the next step is to provide the username that you are trying to create.

Following the username, you can specify the privileges that you want to grant to the new user. Some of the most common are:

There are several other options available, which you can find at the following link: https://www.postgresql.org/docs/12/app-createuser.html.

Creating a database with psql

Similar to creating a user, creating a database is very easy::

If CREATE DATABASE is returned, then it has successfully created the database for you.

Once again let’s take a look at the command that was passed at the psql prompt::

CREATE DATABASE: This is the SQL syntax used to initiate the request for creating the database.

: the name of the database that you want to create, which in my example was “amit”.

If you are creating databases with similar structures, then one of the most useful additions to the CREATE DATABASE argument is the template. You can modify the default database template, template1, in the default installation, and then while trying to replicate it within the same instance you can use the following at the psql prompt::

Using pgAdmin

Creating a user with pgAdmin

As described above, in PostgreSQL a user and a role are the same. Hence, pgAdmin gives the option to create a Login/Role in the options rather than a user. We will be using this option to create additional users. Since in PostgreSQL the users or roles exist at the server level and not at the database level, you will need to right-click on the server in which you want to create the user:

Once you select Login/Group Role, a new window will allow you to provide the name of the user:

After providing the password, proceed to the Privileges tab to provide the privileges that use would need require. These include login, superuser, and creation roles.

After providing the required privileges, you can click Save, and the user will be saved and added at the server level.

Creating a database with pgAdmin

In the pgAdmin window select and log in to the server in which you want to create the database. Once you are in, select the server and right-click for the contextual menu. You will get the create database option:

Selecting Database will open a new window where you can start creating the new database by providing the database’s name of the database and owner:

Name and owner are the minimum requirements to create the database. If you want to do some further customizations, you can select the Definition tab, where you can specify the template, encoding, and custom tablespace :

Click Save and you will find the database available under the Server you selected in the left-hand panel of pgAdmin:

How to Create a Postgres User

Home » Databases » How to Create a Postgres User

User privilege and privilege access management is a crucial security concept for any database type. PostgreSQL handles access control through roles and privileges. For example, adding the LOGIN permission to a role yields a user. Postgres offers several ways to create and manage user accounts.

This tutorial shows how to create and set up different user types for a PostgreSQL database.

Create a New User in PostgreSQL

There are two ways to make a new user in PostgreSQL, and both cases require access to the postgres user.

Note: The postgres user is the PostgreSQL superadmin created during the installation process.

Method 1: Using The createuser Client Utility

The first way to create a new user is with the createuser client utility. This method avoids connecting to the PSQL command-line interface.

To create the user, run the following command in the terminal:

The terminal does not output a message. To echo the server message, add the -e tag:

Alternatively, split the command into two parts:

1. Switch to the postgres user:

2. Run the createuser command:

Postgres automatically creates the user (role with login permissions) in both cases.

Method 2: Using PSQL

The second way to create a new user in PostgreSQL is through the interactive PSQL shell.

1. Switch to the postgres user and start the interactive terminal with:

2. Use the following statement to create a user:

Running the command prints CREATE ROLE to the console. The reason is that the CREATE USER query is an alias for the following command:

Both queries yield the same result.

Create a Superuser in PostgreSQL

To create a superuser in PostgreSQL, you must have the superuser role.

Warning: A database superuser bypasses all checks, which is dangerous from a security aspect. Use this action with care and avoid working with a superuser account unless absolutely necessary.

There are two ways to make a superuser in PostgreSQL:

1. Create a superuser role through the client utility by adding the —superuser tag:

Or use the shorthand tag -s instead of —superuser :

The terminal outputs a message in case of an error or if the user already exists. If successful, no message appears.

2. Alternatively, use the CREATE USER PSQL statement:

The CREATE USER statement is an alias for the following statement:

The CREATE ROLE statement requires adding the LOGIN permission to emulate a user.

Create a Password for the User

Every database user must have a strong password to prevent brute force attacks. PostgreSQL offers two methods to create a user with a password.

Warning: Out of the two methods, the first is preferred and more secure.

1. Use the createuser client utility and add the —pwprompt option to invoke a password creation prompt automatically:

The shorthand version is the -P tag:

The terminal prompts to enter the password twice. The password itself or the length is encrypted and hidden when communicating with the server.

2. Use PSQL to create a user with a password:

If the user already exists, add the password by using ALTER USER :

Password management via PSQL comes with three security vulnerabilities:

Use this method with caution.

Note: Refer to our post to learn more about what a brute force attack is and how it works.

Grant Privileges to the User

By default, new users do not have any privileges except for login. To add privileges when creating a user, run the createuser client utility in the following format:

To do the same in PSQL, run:

Below is a table with commonly used options for both methods.

For example, create a user with create role and database privileges and add the -e tag to echo the results:

Or use the PSQL equivalent:

In both cases, the stated privileges are granted automatically to the new user.

Create a PostgreSQL User Interactively

The interactive user creation is a practical option available only for the client utility. To create a user interactively, run the following command:

The command automatically prompts a question series, asking the following:

Answer yes ( y ) to the superuser question automatically to add the «create database» and «create role» privileges and end the prompt.

List All Users in PostgreSQL

An essential tool for user management in databases is listing all the users with their respective roles and privileges.

To list all users in PostgreSQL, do the following:

1. Connect to the PSQL prompt as the postgres user:

2. List all the users with the following command:

The output shows a table with the role names, attributes (privileges), and the user’s groups. To display the description for each role, add the plus ( + ) sign:

The output shows an additional column with the role description where applicable.

After going through the examples in this guide, you should know how user and role management works in PostgreSQL.

For further reading, learn about how Database-as-a-Service can help improve database management productivity.

How to Create User in PostgreSQL

Many times you may need to create database users in PostgreSQL. Here’s how to create user in PostgreSQL

How to Create User in PostgreSQL

Here are the steps to create user in PostgreSQL.

1. Log into PostgreSQL

Open terminal and run the following command as root user

2. Create User in PostgreSQL

You can create user in interactive mode, or normal mode. We will look at both these methods.

Interactive mode

You will be logged into PostgreSQL as superuser. Run the following command

Postgres will next ask you to enter new user details one by one, as shown below

PostgreSQL will create your user.

Normal mode or non-interactive mode

In this mode PostgreSQL will directly create new user without prompting you for any information.

If you want to create user in a non-interactive manner, just run the following command after Step 1 above.

Replace user_name and mypassword with new user’s username and password respectively.

If you want to grant access to new user to your database sample_db, run the following command

3. List all Users in PostgreSQL

Since you are logged in as superuser, run the \du or \du+ command to list all users in PostgreSQL.

Hopefully, now you can easily create user in PostgreSQL.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!