EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial Postgres Default User
 

Postgres Default User

Priya Pedamkar
Article byPriya Pedamkar

Updated May 18, 2023

Postgres Default User

 

 

Introduction to Postgres Default User

Every database server or cluster has a number of users that can access and manipulate it. In Postgres, too, we can have many users and assign the privileges according to the requirement to those users. These users are different from the users that are available for the operating system login system. The Postgres users can assign the access privileges to other users on the database objects and can own database objects created by them. During the installation of PostgreSQL on your system, the default user named “postgres” is created. You can utilize this default “postgres” user to create other users. It has the access privileges of all the privilege_types and on all the database objects and can create and manipulate databases, tables, schemas, views, stored procedures, functions, sequences, and all other objects of the PostgreSQL database.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

The default user has the password mechanism of ident authentication mode. The default user does not have an explicitly set password. Instead, it relies on the authentication of the user logged into the operating system. The same authentication method is applied to the default user in PostgreSQL, which is the “postgres” user. To log in to PostgreSQL with the default user, it is necessary for the operating system user to have the same name as the default user in PostgreSQL.

Opening Postgres –

You can check after installing PostgreSQL whether a Postgres user is created by running the following command –

cat /etc/passwd

that gives the following output –

Postgres Default User 1

Postgres Default User 2

postgres:x:124:130:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash

the line shows that postgres user is present.

Now, to know the default authentication mechanism that is set for Postgres, you can go and open your pg_hba.conf file, that in my case, is located inside /etc/postgresql/12/main folder as shown as follows –

Postgres Default User 3

After opening this file, you will see the following lines that show the authentication mode used for postgres –

# Database administrative login by Unix domain socket

local all postgres ident

# TYPE DATABASE USER ADDRESS METHOD

# “local” is for Unix domain socket connections only

local all ident

# IPv4 local connections:

host all 127.0.0.1/32 ident

# IPv6 local connections:

host all::1/128 ident

Postgres Default User 4

Alternatively, you can check the authentication mode for Postgres user in the PostgreSQL database server from command-line using the following command –

cat /etc/postgresql/12/main/pg_hba.conf

that gives the following output:

Postgres Default User 5

ident authentication mode matches the credentials of the operating system’s currently logged in user and the user with which you are logging in to Postgres. You can change this authentication mode to md5 or any other authentication mode you wish to.

Now, let us login to PostgreSQL by typing the following command to enter into the Postgres terminal –

sudo su – postgres

Then enter the sudo password.

To enter into the psql command-prompt terminal shell, enter the following command –

psql

and enter the password if prompted.

command

After installing PostgreSQL, you can check the list of the users present in your PostgreSQL database server by firing the \du meta-command or select a command on the pg_user table that stores the information of users.

\du gives the following output –

\du

command 1

Alternatively, firing the select command on the pg_user table to retrieve usename column value in the following way –

SELECT usename FROM pg_user;

gives you the following result on the terminal.

command 2

From both the queries, we can conclude that only one default user is present in the PostgreSQL database server named Postgres. This is the superuser.

To check the privileges assigned to a user, you can fire the queries on the table_privileges table that stores the information related to accessing the privileges of all the users. To check the access privileges present for our default user, we can fire the select query on the table_privileges table.

We can check that by firing the following query –

SELECT table_schema as schema, table_name as table, privilege_type as privilege
FROM information_schema.table_privileges
WHERE grantee = 'postgres';

that gives the following output –

command 3

You can create a new user after logging in with postgreuser by using the command to create a user in the following way. To create a new user having the name as Payal, you can fire the following command –

CREATE USER payal;

that gives the following result as output –

create user

To check the privileges assigned to the “payal” user after creating it, you can execute the “\du” command.

\du

or

select usename from pg_user;

gives the following output –

create use 2

and then for other privileges, check to use the following select query –

SELECT table_schema as schema, table_name as table, privilege_type as privilege
FROM information_schema.table_privileges
WHERE grantee = 'payal';

gives the output as follows –

create user 3

When creating a new user, it can be observed that no privileges are initially assigned to the user. You can assign privileges of different privilege types by using GRANT and REVOKE commands. The superuser should only be used when executing certain activities that need greater rights; you should never manipulate the database with this user.

You can change the default user to some other user than Postgres by changing the value of the environment variable PGUSER. This allows the target user of your choice to override the default user.

Conclusion

After installing PostgreSQL on our system, the default user created is “postgres,” and we log in to the PostgreSQL database server using this default user. The Postgres user is the superuser and has access privileges of inserting, updating, deleting, and selecting the database objects such as tables, columns, views, sequences, schemas, functions, stored procedures, etc. We can check the authentication mode that Postgres uses by checking the contents of the pg_hba.conf configuration file. By default, the authentication method is set to “ident” after installation. However, you have the flexibility to change it to “md5” or any other desired authentication mechanism if you prefer.

The default user with which you are logging in to Postgres can be changed by changing the environment variable named PGUSER. You should always work and manipulate on PostgreSQL database with a user other than superuser and should switch to superuser if and only if you want to perform a certain operation with higher privilege. The scope of all the users in Postgres is of the whole database server or cluster, if present.

Recommended Articles

We hope that this EDUCBA information on “Postgres Default User” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Postgres Delete Cascade
  2. Identity Column PostgreSQL
  3. PostgreSQL Roles
  4. PostgreSQL MIN()

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW