How To Define, Add, Create User For Postgres or PostgreSQL Server? – POFTUT

How To Define, Add, Create User For Postgres or PostgreSQL Server?


PostgreSQL Database Server have a lot of tools to manage the suite. Creating user is one of the most popular issue to manage Postgres Database. In this tutorial we will examine how to create user with different rights, privileges, database. We will mainly follow createuser tool for these and createuser is a wrapper for the CREATE ROLE Sql statement. By the way we have allready examined the PostgreSQL Database Server installation in the following tutorial.

How To Install PostgreSQL Server Into Linux, Debian, Ubuntu, CentOS, Mint, Fedora, Redhat?

Change Shell To Postgres User

PostgreSQL Database server can be accessed with postgres  user which is created during database installation. We can use sudo  command with -i and -u  parameters by specifying username postgres like below.

$ sudo -i -u postgres

Create User with Interactive Questions

The most basic and default scenario for the createuser is interactive questions. In this scenario we do not specify any option. We will only provide the user name we want to create. In the flow we will answer required questions line by line. In this example we will create a user named ismail . We can also provide --interactive option.

$ createuser ismail
$ createuser --interactive ali
Create User with Interactive Questions
Create User with Interactive Questions

Create User Which Do Not Have Create User Privileges

We can create a user by providing password. In this example we will use -P -s and -e options and create user named ahmet . We will be prompted the password and the create role command will be printed with the hash of the password we provided.

$ createuser -P -s -e ahmet
Create User Which Do Not Have Create User Privileges
Create User Which Do Not Have Create User Privileges

The create role sql will be like below.

CREATE ROLE ahmet PASSWORD 'md526c4663b7303e420de67f00203c9f179' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;

Create User With No Login

Sometimes we may need new user without login. This user will be very useful as a means of managing database permissions. We can use --no-login  option like below.

$ createuser --no-login -P -s -e ahmet

LEARN MORE  How To List MySQL Database Users?

Leave a Comment