Create user postgresql mac

Create user postgresql mac

createuser — define a new PostgreSQL user account


createuser [ connection-option . ] [ option . ] [ username ]


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.

createuser is a wrapper around the SQL command CREATE ROLE . There is no effective difference between creating users via this utility and via other methods for accessing the server.


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.

Indicates role to which this role will be added immediately as a new member. Multiple roles to which this role will be added as a member can be specified by writing multiple -g switches.

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.

Prompt for the user name if none is specified on the command line, and also prompt for whichever of the options -d / -D , -r / -R , -s / -S is not specified on the command line. (This was the default behavior up to PostgreSQL 9.1.)

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.

The new user will have the REPLICATION privilege, which is described more fully in the documentation for CREATE ROLE .

The new user will not have the REPLICATION privilege, which is described more fully in the documentation for CREATE ROLE .

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

Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.

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

This option is never essential, since createuser will automatically prompt for a password if the server demands password authentication. However, createuser will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt.


Default connection parameters

Specifies whether to use color in diagnostic messages. Possible values are always , auto and never .

This utility, like most other PostgreSQL utilities, also uses the environment variables supported by libpq (see Section 34.15).


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.


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 same user joe using the server on host eden , port 5000, with attributes explicitly specified, taking a look at the underlying command:

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.

Читайте также:  Visual studio создать базу данных sqlite

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


Getting Started with PostgreSQL on Mac OSX

The other day I began a new Node.js project and wanted to use PostgreSQL as my database backend. If you’re here reading this tutorial, hopefully, you are trying to do the same!

This tutorial will teach you how to set up, configure, and use PostgreSQL on MacOSX 10.7 (Lion) and above. You will need at least a basic level of comfort using the command line using either the MacOSX built-in terminal, iTerm2, Zsh, or something similar. By the time we are finished, you will:

  • Have a running PostgreSQL 9.5.4 instance
  • Know how to create and manage databases and users
  • Know how to use both command-line and UI tools to manage your database

PostgreSQL is an ACID-compliant Object Relational Database Management System, or ORDBMS (quite a mouthful!). Put simply, it is a database that allows you to relate one piece of data to another (thus, “relational”). It runs on nearly any operating system including Linux, Unix, and Windows. It is high performance and highly scalable, capable of handling huge amounts of data and high-load internet applications with thousands of concurrent users. Its unique combination of simplicity and power makes it a popular choice for individual users and small businesses, but enterprise businesses like Yahoo, Uber, Disqus, and TripAdvisor as well.

Postgres supports a long list of database features, including several enterprise features. Aside from standard relational database features, some of the most notable features in Postgres are:

  • Streaming replication
  • Schemas
  • User-defined objects like operators, data types, and functions
  • Nested transactions
  • Table inheritance
  • Partitioning
  • Several unusual data types, like Money, Geometry, IP addresses, JSON, and data ranges.
  • Can execute stored procedures in over a dozen programming languages, including Java, Perl, Python, Ruby, and C/C++.

Due to its first-class support for JSON, Postgres is often a good alternative to “No-SQL” databases like MongoDB. Read more about PostgreSQL

The first thing we’re going to do is install Postgres. There are two main ways to get Postgres onto your machine:

  1. Using a graphical installer like BigSQL or
  2. Using a package manager to install via the command line.

You can pick whichever option is right for you. For this tutorial, let’s see how to install on the command line.

To install PostgreSQL on the command line we will be using a package manager called Homebrew. If you already have Homebrew installed, you can skip to Installing PostgreSQL. Otherwise, let’s keep going.

Let’s start by copying and pasting the following command into our command line:

Don’t worry about what all that does—as long as you’re using MacOSX 10.7 or higher, the Ruby interpreter comes pre-installed. The rest of this command (the curl command) is a tool to make web requests from your command line. This one downloads the script at the given URL, and then the Ruby interpreter executes it. You should see output similar to the following:

Installing Homebrew

Now that we have Homebrew, we can proceed to install Postgres!

Now that we have Homebrew, installing PostgreSQL is very easy. Run the following command to install PostgreSQL using Homebrew:

You should then see the following output:

*Installing *

Homebrew takes care of resolving Postgres’ dependencies, which in this case are OpenSSL and Readline. It also takes care of knowing which are the latest versions, where to get them, and how to unpack them. Most importantly are the easy-to-miss instructions at the very end of the install:

Starting Services

Let’s go ahead and start Postgres running, and make sure Postgres starts every time your computer starts up. Execute the following command:

Finally, let’s make sure Postgres is installed and running. Let’s check what version is running:

Your output should look like the following:

Postgres Installed

That’s it! You have Postgres installed. Now we’re finally ready to learn how to use it!

Postgres works pretty hard to make itself usable right out of the box without you having to do anything. By default, it automatically creates the user postgres . Let’s see what other users it has created. Let’s start by using the psql utility, which is a utility installed with Postgres that lets you carry out administrative functions without needing to know their actual SQL commands.

Start by entering the following on the command line:

(You may need to use sudo psql postgres for this command to work, depending on how your system is configured).

You’ll see output like the following:

psql output

That’s the psql command line. We can now enter a command to see what users are installed:

Under the covers, this command executes an SQL query (we’ll learn about those later) that gets all the users in the database. On my machine, it returns the following:

psql User List

We see the Postgres user I mentioned, but what is that other user, engineerapart ? This is one of the things Postgres does to make your life easier when you first install it. On most Linux-based operating systems, the username that is used by default by all processes is the one you are logged in as. You don’t have to pass your username to most programs. But if a particular program, like Postgres, doesn’t have your username configured—it will fail!

Читайте также:  Как подключить принтер hp laser 100 series к wifi

So when Postgres is installed, it automatically creates a database user that matches your username, so that you can get started right away.

Postgres doesn’t actually directly manage users or groups, like most standard permission models do. Instead, it directly manages what it calls roles.

While it is certainly convenient that Postgres sets up a set of default users for you, it is a very bad idea to use them for anything except local development, because they are very widely known and more importantly, they are super user accounts—they can do anything, including delete databases. This is not safe for a production database—we need users with limited permissions. So how do we create and use new users (roles)?

There are two main ways to do this:

  • Directly execute the CREATE ROLE SQL query on the database
  • Use the createuser utility that comes installed with Postgres (which is just a wrapper for executing CREATE ROLE ).

Let’s look at both cases.

The basic syntax for CREATE ROLE looks like this:

Where username is the user you want to create, and the password goes at the end in quotes. We will get to the options later.

Let’s start by logging in again to our helpful psql tool:

psql output

While we’re in here, let’s set the password for the default postgres account—by default, it has no password.

You will be prompted to enter the password and confirm it. Now let’s create our new role:

Your output should look like the following:


Wait. The attributes list for the user patrick is completely empty. Why?

This is how Postgres securely manages defaults. This user can read any database, table, or row it has permissions for, but nothing else—it cannot create or manage databases and has no admin powers. This is a good thing! It helps keep your database secure.

So let’s add the CREATEDB permission to our new user to allow them to create databases:

Your output should like this:


Postgres ships with a number of very useful command line utilities that make the above process much easier. Instead of logging into psql, executing SQL queries, and needing to know the details of the query statements, you can use a familiar command line interface to do the same tasks. A few of these tools are:

  • createuser: creates a user
  • createdb: creates a database
  • dropuser: deletes a user
  • dropdb: deletes a database
  • postgres: executes the SQL server itself (we saw that one above when we checked our Postgres version!)
  • pg_dump: dumps the contents of a single database to a file
  • pg_dumpall: dumps all databases to a file
  • psql: we recognize that one!

So let’s use createuser to do the same thing we did above: create the patrick user:

This creates the user patrick with all of the default attributes, again, without the ability to create databases. If we wanted the ability to create a database, you would execute the following instead:

Unfortunately, there is no command line tool to accomplish the same thing as ALTER ROLE. To change users after they are created, you must use psql .

That’s it! Now our new user is set up and can create databases. Let’s start managing our database with that new user.

Just like creating a user, there are two ways to create a database:

  • Executing SQL commands directly with psql
  • The createdb command line utility.

The core SQL syntax for creating a database in PostgreSQL is:

We’ll go through the same process as above:

psql user login

You’ll notice the prompt is slightly different – the # has changed to a > . This indicates you’re no longer using a Super User account.

Once this is done, you need to add at least one user who has permission to access the database (aside from the super users, who can access everything). To do that, we’re going to learn a new SQL command:

Here, I have also shown you a few new commands that can be used with psql :

  • \list : lists all the databases in Postgres
  • \connect : connect to a specific database
  • \dt : list the tables in the currently connected database


You can now create, read, update and delete data on our super_awesome_application database with the user patrick !

As above, creating a database using createdb is simpler, with the caveat that you cannot manage the database after it is created. For that, psql is required.

Here, I have invoked the createdb utility and passed it the patrick user to use for connecting to the database. It is that user whose permissions will be checked to execute the create command.

It is very, very rare that you will want to change a database after it is created. The only things you can change are its name and some configuration parameters. Changing configuration parameters (such as collation or character sets) have implications that go far outside of this tutorial. However, we can change the name if we’d like.

Again, there is no command line tool to change a database once it’s created. We must use psql :

And the output should be similar to:

Читайте также:  Как пользоваться сканером на принтере hp deskjet 2320


That’s it! We have created our user, created a database, and given the user permissions to use the database. Along the way, we learned to use some of the pre-installed Postgres command line tools. This will give us a great foundation for understanding more complex behavior in the next section.

Now let’s take a look at some popular graphical tools for managing PostgreSQL on MacOSX.

So far, we have focused pretty heavily on the command line tools for managing our Postgres installation. However, for some things, that is laborious and time-intensive: For example, if you want a quick view of the data you have in a table, getting that out of the command line tools takes more time than using a GUI. Do it 100 times a day while developing, that time starts to add up!

So naturally, a bunch of enterprising developers has built some pretty impressive GUIs that can be used to managed your local (and remote!) Postgres servers. We’ll take a look at a few of the most popular and user-friendly.

Postico is a modern Postgres client for OSX, built by the same developer who built (mentioned above). It is free, but you can buy a license to unlock additional power features. This is the GUI that I use to manage Postgres because it is built specifically for Mac and has a beautiful, very easy to use (but powerful) UI. It also includes an SQL editor for complex queries.

To get started with Postico, simply:

  • Download it at
  • Double-click the downloaded Zip file in Finder
  • Drag the extracted file to your Applications folder
  • Find Postico in Launchpad and launch the app.

You’ll see a screen that looks like the following (without any database connections configured):


To connect to your local database:

  • Click on ‘New Favorite’
  • Give it an easy to remember name
  • You can leave the default values entered in the boxes
  • If you changed the postgres user password above, enter it in the password box
  • Drop down the Options and select “Show All Databases” – otherwise you’ll wonder where your databases are!

Postico new favorite

  • Click ‘Done’ to save it
  • Then click the ‘Connect’ button.
  • You’re done!

Postico connected

Read the Postico Documentation to learn how to use Postico’s amazing features!

pgAdmin is the oldest of the Postgres GUIs, its first version being released just a few months after Postgre’s first release in 1996. Having been rewritten several times, it can run on Linux, MacOSX, and Windows, and features powerful database management tools including a syntax-highlighted SQL editor. Designed to run on both client machines and on deployed servers, pgAdmin is capable of handling advanced cases that Postico cannot.

To get started with pgAdmin, simply:

  • Download it at
  • Double-click the downloaded disc image (DMG) file in your Downloads folder
  • Drag the pgAdmin 4 app to your Applications folder
  • Find pgAdmin in Launchpad and launch the app.

You’ll see a screen that looks like the following:


To connect to your local database:

  • Right-click on ‘Servers’ and select Create =>Server
  • Give it an easy to remember name and uncheck the “Connect Now” box
  • For server address, enter localhost
  • You can leave the default values entered in the boxes
  • If you changed the postgres user password above, enter it in the password box

Postico new favorite

  • Click ‘Save’ to save it
  • Your new server will appear in the list of servers
  • Click your new server and enter the password one more time to connect
  • You’re done!

pgAdmin connected

Read the pgAdmin Documentation to learn how to use Postico’s amazing features!

Navicat is without a doubt one of the best Enterprise-level management GUIs for PostgreSQL. Featuring not only an SQL editor and database, table and row viewing and management, Navicat also features a Data Modeler that allows you to graphically design and debug your database schemas. However, although Navicat comes with a 14-day free trial, its licenses range from $100-$250.

To get started with Navicat, simply:

  • Download it at
  • Double-click the downloaded disc image (navicat112pgsqlen.dmg) file in your Downloads folder
  • Drag the Navicat app to your Applications folder
  • Find Navicat in Launchpad and launch the app.

You’ll see a screen that looks like the following:


To connect to your local database:

  • Click “Connection”
  • Fill out the dialog provided using the same information as above
  • Click “Ok” to save the connection.
  • Double-click the new connection in the list on the left, and you’re connected. That’s it!

Navicat connect

Read the Navicat Online Manual to learn how to use Postico’s amazing features!

PostgreSQL is one of the top relational databases in the world, used by individuals, small businesses, and huge enterprises alike. Despite its high performance and powerful feature set, it is incredibly easy to get started with it. In this tutorial, we have learned how to:

  • Install PostgreSQL server on MacOSX using Homebrew
  • Use the Postgres command line tools to configure Postgres:
    • Use the psql command line tool to view information about the database
    • Create a new user using psql and createuser
    • Change a user’s password
    • Create a database using psql and createdb
    • Change a database name
    • Grant access permissions on a database to a specific user

We have also taken a brief look at some popular GUI-based management tools for Postgres, including Postico, pgAdmin and Navicat.


Поделиться с друзьями