Posted in PostgreSQL

3 easy ways to create users in Postgresql

   July 30, 2020  Leave a comment
3 easy ways to create users in Postgresql database

Like many other databases, create a user in PostgreSQL is pretty easy. In this short tutorial, we will be looking at 3 easy ways to create users in PostgreSQL with examples.

pgAdmin GUI

The first option in our tutorial “3 easy ways to create users in PostgreSQL” is to create a user using the pgAdmin Graphical User interface. Using the graphical user interface is pretty straight forward and easy.

To begin:

  • Connect to your server
  • Extend the server to view the “Databases”, “Login/Group Roles” and “Tablespaces” options
  • Right click on “Login/Group Roles” > Create >  Login/Group Roles
  • Enter the necessary parameters in the tabs in the new window shown.

For a working example of this, please see the animated GIF below.

3 easy ways to create users in Postgresql database
How to Create user in postgresql using pgAdmin GUI

[section label=”Command-line”]

Command-line

The second option in our tutorial “3 easy ways to create users in PostgreSQL” is to create a user using the Linux command-line.

First, log on to your operating system and connect as Postgres user.

sudo su postgres

Once connected, run the command below and follow the instructions on the screen to create the user:

createuser --interactive --pwprompt

Output to expect:

Enter name of role to add: testuser1

Enter password for new role:

Enter it again:

Shall the new role be a superuser? (y/n) n

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

How to create a user in postgresql using the command-line

Query Tool

The third option is our tutorial “3 easy ways to create users in PostgreSQL” is to create a user using the query tool. This can be done using the query tool in pgAdmin and the command-line query tool (psql)

 

Create user with no password:

CREATE USER testuser;

Create user with a password:

CREATE USER testuser WITH PASSWORD 'testpassword';

Create a user that expires on a given date:

CREATE USER testuser WITH PASSWORD 'testpassword' VALID UNTIL '2020-01-01';

Create a user that can create databases:

CREATE USER testuser WITH PASSWORD 'testpassword' CREATEDB;

Create a User with more options:

CREATE ROLE "QueryUser" WITH
    LOGIN
    NOSUPERUSER
    CREATEDB
    CREATEROLE
    INHERIT
    NOREPLICATION
    CONNECTION LIMIT -1
    VALID UNTIL '2020-08-03T10:20:20-05:00' 
    PASSWORD 'test';
Create a user in postgresql using the query tool
Create a user in postgresql using the query tool

Conclusion

For more information on how to create users in PostgreSQL please refer to the Official PostgreSQL Online Manual.  Check out our other post on 20 PostgreSQL basic administrative commands you need to know.