Create a New User in PostgreSQL

Note: This post only provides the key commands of the postgreSQL as a easy way for developers. If you need to learn in depth, please refer to the official documentation

Create a new user in PostgreSQL

CREATE USER <name> WITH PASSWORD ‘<password>’;

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

ALTER USER <name> WITH PASSWORD ‘<password>’;

Followings are the available parameters.

Option Syntax	PSQL	Desc
`-s` `--superuser`	`SUPERUSER`	Add the superuser privilege.
`-S` `--no-superuser`	`NOSUPERUSER`	No superuser privilege (default).
`-d` `--createdb`	`CREATEDB`	Allows the user to create databases.
`-D` `--no-createdb`	`NOCREATEDB`	Not allowed to create databases (default).
`-r` `--createrole`	`CREATEROLE`	Allows the user to make new roles.
`-R` `--no-createrole`	`NOCREATEROLE`	Not allowed to create roles (default).
`-i` `--inherit`	`INHERIT`	Automatically inherit the privileges of roles (default).
`-I` `--no-inherit`	`NOINHERIT`	Do not inherit privileges of roles.
`-l` `--login`	`LOGIN`	Allows the user to log into a session with the role name (default).
`-L` `--no-login`	`NOLOGIN`	Not allowed to log into a session with the role name.
`--replication`	`REPLICATION`	Allows initiating streaming replication and activating/deactivating backup mode.
`--no-replication`	`NOREPLICATION`	Not allowed to initiate streaming replication or backup mode (default).
`-P` `--pwprompt`	`PASSWORD '<password>'`	Initiates password creation prompt or adds provided password to the user. Avoid using this option to create a passwordless user.
/	`PASSWORD NULL`	Specifically sets the password to null. Every password authentication fails for this user.
`-c <number>` `--connection-limit=<number>`	`CONNECTION LIMIT <number>`	Sets the maximum number of connections for user. Default is without limit.

Creating a user with role and password in PostgreSQL

CREATE USER <name> WITH CREATEROLE CREATEDB;

CREATE USER <name> WITH PASSWORD ‘<password>’; WITH SUPERUSER;

List all users in PostgreSQL

\du

List all databases in PostgreSQL

`\l`
Switch Database

\c sales

List all tables

`sales=# \dt`

Create a database in PostgreSQL

CREATE DATABASE name [ WITH ] [ OWNER [=] user_name ] [ TEMPLATE [=] template ] [ ENCODING [=] encoding ] [ STRATEGY [=] strategy ] ] [ LOCALE [=] locale ] [ LC_COLLATE [=] lc_collate ] [ LC_CTYPE [=] lc_ctype ] [ ICU_LOCALE [=] icu_locale ] [ LOCALE_PROVIDER [=] locale_provider ] [ COLLATION_VERSION = collation_version ] [ TABLESPACE [=] tablespace_name ] [ ALLOW_CONNECTIONS [=] allowconn ] [ CONNECTION LIMIT [=] connlimit ] [ IS_TEMPLATE [=] istemplate ] [ OID [=] oid ]

create database demo with owner = demo

name

The name of a database to create.

user_name

The role name of the user who will own the new database, or DEFAULT to use the default (namely, the user executing the command). To create a database owned by another role, you must be a direct or indirect member of that role, or be a superuser.

template

The name of the template from which to create the new database, or DEFAULT to use the default template (template1).

encoding

Character set encoding to use in the new database. Specify a string constant (e.g., 'SQL_ASCII'), or an integer encoding number, or DEFAULT to use the default encoding (namely, the encoding of the template database). The character sets supported by the PostgreSQL server are described in Section 24.3.1. See below for additional restrictions.

strategy

Strategy to be used in creating the new database. If the WAL_LOG strategy is used, the database will be copied block by block and each block will be separately written to the write-ahead log. This is the most efficient strategy in cases where the template database is small, and therefore it is the default. The older FILE_COPY strategy is also available. This strategy writes a small record to the write-ahead log for each tablespace used by the target database. Each such record represents copying an entire directory to a new location at the filesystem level. While this does reduce the write-ahead log volume substantially, especially if the template database is large, it also forces the system to perform a checkpoint both before and after the creation of the new database. In some situations, this may have a noticeable negative impact on overall system performance.

locale

This is a shortcut for setting LC_COLLATE and LC_CTYPE at once.

Create a New User in PostgreSQL

Tech Bootstrap