PostgreSQL - CREATE USER

PostgreSQL ? CREATE USER

PostgreSQL ? Create User

To create a user in PostgreSQL, you can use CRATE USER query. It also has many options to grant roles or enable encryption, set password and such for the user.

Syntax to Create User

Following is the syntax of CREATE USER query.

CREATE USER name [ [ WITH ] option [ ... ] ] CwRhEeAreTEopUtSioEnR cnaanmbee[: [ WITH ] option [ ... ] ] wherSeYoSpItDiounidcan be:

| CREATEDB | NOCREATEDB

| CSRYESAIDTEuRidOLE | NOC|R| EICNARTGEERAROTOUELDPEB | NOCREATEDB grou|pnCaRmEeAT[,ER..O.]LE | NOCREATEROLE

| [ ENCRYPTED | UNEN|CIRNYGPRTOEDUP] groupname [, ...] PASS|W[ OERNDCR'pYaPsTsEwDor|d'UNENCRYPTED ] PASSWORD 'password'

| VALID UNTIL 'abst|imVeA'LID UNTIL 'abstime'

We will see the usage of the options using examples.

Example ? Create User in PostgreSQL (Without any options)

Let us create a user without any options. Just like that.

CREATE USER lini;

CREATE USER lini;

Output When you list the users from psql shell, the table contains our newly added user. Also, as we have not mentioned any options while creating this user, the List of roles Attributes column is empty for the user.

Example ? Create User with Attribute to Create DB

This example shows to CREATE USER with attribute CREATEDB. This means, that this user has the privilege to create databases.

CREATE USER lini WITH CREATEDB; CREATE USER lini WITH CREATEDB;

Run this query in Query Tool.

Check the users list in psql shell.

The user is created with the Create DB under the List of roles . Note: If you do not mention the option CREATEDB while creating the user, NOCREATEDB will be applied to the user and the user cannot create databases.

Example ? Create User with Attribute to Create Roles

We can create users with option to give them privilege to create roles.

CREATE USER lini WITH CREATEROLE; CREATE USER lini WITH CREATEROLE;

Note: If you do not mention the option CREATEROLE while creating the user, NOCREATEROLE will be applied to the user and the user cannot create roles.

Example ? Create User with multiple Attributes

You can provide multiple options while creating a user. In the following example, we created user lini with options that enable the user to create database and create

users.

CREATE USER lini WITH CREATEDB CREATEROLE; CREATE USER lini WITH CREATEDB CREATEROLE;

Run the query in psql shell or Query Tool.

When you list the user in psql shell, you would see the user created with the provided options.

Summary

In this tutorial, we have created a user with different roles or options using CREATE USER query.

P o s t g re SQ L

PostgreSQL Tutorial PostgreSQL - Install PostgreSQL GUI PostgreSQL - psql Shell - Commands PostgreSQL - Create Database PostgreSQL - Select Database PostgreSQL - List Databases PostgreSQL - Drop Database PostgreSQL - Create Table PostgreSQL - Describe Table PostgreSQL - SHOW Tables PostgreSQL - Drop Table PostgreSQL - INSERT INTO Table PostgreSQL - SELECT FROM Table PostgreSQL - WHERE PostgreSQL - LIKE PostgreSQL - JOIN PostgreSQL - WITH PostgreSQL - UPDATE PostgreSQL - UPDATE FROM SELECT PostgreSQL - ADD COLUMN PostgreSQL - DELETE COLUMN PostgreSQL - PRIMARY KEY PostgreSQL - FOREIGN KEY PostgreSQL - SELECT INTO PostgreSQL - Current Date PostgreSQL - LIMIT PostgreSQL - HAVING PostgreSQL - DISTICNT

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download