EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

Postgres Default User

By Priya PedamkarPriya Pedamkar

Home » Data Science » Data Science Tutorials » PostgreSQL Tutorial » Postgres Default User

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 him/her. But who is the first user using which the other users are created? Whenever you install PostgreSQL in your system a default user named Postgres is also created. 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 PostgreSQL database.

The default user has the password mechanism of ident authentication mode. That means the password is not set to the default user. It depends on the user with which you are logged in the operating system and the same authentication is practiced for the default user of the PostgreSQL that is Postgres user. Hence, if you wish to log in to PostgreSQL with the default user then the user with which you are logged in the operating system should be present with the same name in PostgreSQL.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Opening Postgres –

You can check after installing PostgreSQL whether 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

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 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 authentication mode for Postgres user in 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 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 Postgres terminal –

sudo su – postgres

Then enter the sudo password.

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

psql

and enter the password if prompted.

command

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

\du gives following output –

\du

command 1

Alternatively, firing the select command on 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 what privileges are assigned to the payal user after creating it you can fire the \du command

\du

or

select usename from pg_user;

gives following output –

create use 2

and then for other privileges check use 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

As can be seen, no privileges are assigned to the new user when created. You can assign privileges of different privilege types by using GRANT and REVOKE commands. You should always do manipulations on the database by using a user other than the superuser and use the superuser only when performing certain operations that require higher privileges.

You can change the default user to some other user than Postgres by changing the value of the environment variable PGUSER. This makes the default user to be overridden by the target user that you wish to.

Conclusion

Postgres is the default user that is created and with which you log in to your PostgreSQL database server by default after the installation of PostgreSQL in our system. 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 is used by Postgres by checking the contents of the pg_hba.conf configuration file. By default, after installation, the authentication method is set to ident that can be changed further to md5 or any other authentication mechanism if you wish to.

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

This is a guide to Postgres Default User. Here we discuss the introduction, syntax, command with examples, and its code implementation. You may also have a look at the following articles to learn more –

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

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
PostgreSQL Tutorial
  • Basic
    • What is PostgreSQL
    • PostgreSQL Features
    • How to Install PostgreSQL
    • PostgreSQL Versions
    • PostgreSQL Architecture
    • PostgreSQL GUI
    • Postgres Command-Line
    • PostgreSQL Variables
    • PostgreSQL Data Types
    • PostgreSQL NOT NULL
    • PostgreSQL Integer
    • PostgreSQL Boolean
    • PostgreSQL BIGINT
    • PostgreSQL NULLIF
    • PostgreSQL Administration
    • PostgreSQL Commands
    • PostgreSQL Operators
    • PostgreSQL IN Operator
    • Postgres like query
    • PostgreSQL encode
    • PostgreSQL Cheat Sheet
    • PostgreSQL List Databases
    • PostgreSQL Rename Database
  • Control Statement
    • PostgreSQL IF Statement
    • PostgreSQL if else
    • PostgreSQL CASE Statement
    • PostgreSQL LOOP
    • PostgreSQL For Loop
    • PostgreSQL While Loop
  • Joins
    • Joins in PostgreSQL
    • PostgreSQL Inner Join
    • PostgreSQL Outer Join
    • LEFT OUTER JOIN in PostgreSQL
    • PostgreSQL FULL OUTER JOIN
    • PostgreSQL LEFT JOIN
    • PostgreSQL Full Join
    • PostgreSQL Cross Join
    • PostgreSQL NATURAL JOIN
    • PostgreSQL UPDATE JOIN
  • Queries
    • PostgreSQL Queries
    • PostgreSQL INSERT INTO
    • PostgreSQL WHERE Clause
    • PostgreSQL WITH Clause
    • PostgreSQL ORDER BY
    • PostgreSQL ORDER BY Random
    • PostgreSQL ORDER BY DESC
    • PostgreSQL GROUP BY
    • PostgreSQL group_concat
    • PostgreSQL HAVING
    • PostgreSQL Recursive Query
  • Advanced
    • PostgreSQL Schema
    • Postgres List Schemas
    • PostgreSQL Drop Schema
    • PostgreSQL VARCHAR
    • Array in PostgreSQL
    • PostgreSQL DDL
    • PostgreSQL List Users
    • Postgres Default User
    • Postgres add user
    • PostgreSQL User Password
    • PostgreSQL log_statement
    • PostgreSQL repository
    • PostgreSQL shared_buffer
    • PostgreSQL String Functions
    • PostgreSQL Compare Strings
    • PostgreSQL Text Search
    • PostgreSQL TEXT
    • PostgreSQL String Array
    • PostgreSQL where in array
    • PostgreSQL Constraints
    • PostgreSQL UNIQUE Constraint
    • PostgreSQL CHECK Constraint
    • PostgreSQL INTERSECT
    • PostgreSQL Like
    • Cursors in PostgreSQL
    • PostgreSQL UNION ALL
    • Indexes in PostgreSQL
    • PostgreSQL Index Types
    • PostgreSQL REINDEX
    • PostgreSQL UNIQUE Index
    • PostgreSQL Clustered Index
    • PostgreSQL DROP INDEX
    • PostgreSQL DISTINCT
    • PostgreSQL FETCH
    • PostgreSQL RAISE EXCEPTION
    • PostgreSQL Auto Increment
    • Sequence in PostgreSQL
    • Wildcards in PostgreSQL
    • PostgreSQL Subquery
    • PostgreSQL Alias
    • PostgreSQL LIMIT
    • PostgreSQL Limit Offset
    • PostgreSQL LAG()
    • PostgreSQL Table
    • Postgres Show Tables
    • PostgreSQL Describe Table
    • PostgreSQL Lock Table
    • PostgreSQL ALTER TABLE
    • Postgres Rename Table
    • PostgreSQL List Tables
    • PostgreSQL TRUNCATE TABLE
    • PostgreSQL Table Partitioning
    • Postgres DROP Table
    • PostgreSQL Functions
    • PostgreSQL Math Functions
    • PostgreSQL Window Functions
    • Aggregate Functions in PostgreSQL
    • PostgreSQL Primary Key
    • Foreign Key in PostgreSQL
    • PostgreSQL Procedures
    • PostgreSQL Stored Procedures
    • PostgreSQL Views
    • PostgreSQL Materialized Views
    • Postgres Create View
    • PostgreSQL Triggers
    • PostgreSQL DROP TRIGGER
    • PostgreSQL Date Functions
    • PostgreSQL TO_DATE()
    • PostgreSQL datediff
    • PostgreSQL Timestamp
    • PostgreSQL CURRENT_TIMESTAMP()
    • PostgreSQL Notify
    • PostgreSQL LENGTH()
    • PostgreSQL blob
    • PostgreSQL Median
    • PostgreSQL kill query
    • PostgreSQL Formatter
    • PostgreSQL RANK()
    • PostgreSQL Select
    • PostgreSQL Average
    • PostgreSQL DATE_PART()
    • PostgreSQL EXECUTE
    • PostgreSQL COALESCE
    • PostgreSQL EXTRACT()
    • PostgreSQL Sort
    • PostgreSQL TO_CHAR
    • PostgreSQL Interval
    • PostgreSQL Number Types
    • PostgreSQL ROW_NUMBER
    • Alter Column in PostgreSQL
    • PostgreSQL Identity Column
    • PostgreSQL SPLIT_PART()
    • PostgreSQL CONCAT()
    • PostgreSQL replace
    • PostgreSQL TRIM()
    • PostgreSQL MAX
    • PostgreSQL DELETE
    • PostgreSQL Float
    • PostgreSQL OID
    • PostgreSQL log
    • PostgreSQL REGEXP_MATCHES()
    • PostgreSQL MD5 
    • PostgreSQL NOW()
    • PostgreSQL RANDOM
    • PostgreSQL round
    • PostgreSQL Trunc()
    • PostgreSQL TIME
    • PostgreSQL IS NULL
    • PostgreSQL CURRENT_TIME
    • PostgreSQL MOD()
    • Postgresql Count
    • PostgreSQL Datetime
    • PostgreSQL MIN()
    • PostgreSQL age()
    • PostgreSQL enum
    • PostgreSQL OR
    • PostgreSQL Wal
    • PostgreSQL NOT IN
    • PostgreSQL SET
    • PostgreSQL Current Date
    • PostgreSQL Compare Date
    • PostgreSQL SERIAL
    • PostgreSQL UUID
    • PostgreSQL Merge
    • PostgreSQL Database
    • PostgreSQL Clone Database
    • PostgreSQL Copy Database
    • PostgreSQL Show Databases
    • PostgreSQL Restore Database
    • PostgreSQL DROP DATABASE
    • PostgreSQL ALTER DATABASE
    • Postgres DROP Database
    • Postgres Dump Database
    • PostgreSQL OFFSET
    • PostgreSQL GRANT
    • PostgreSQL COMMIT
    • PostgreSQL ROLLUP
    • PostgreSQL JSON
    • EXPLAIN ANALYZE in PostgreSQL
    • PostgreSQL Temporary Table
    • PostgreSQL Show Tables
    • PostgreSQL cluster
    • PostgreSQL Replication
    • PostgreSQL Logical Replication
    • PostgreSQL flush privileges
    • PostgreSQL Tablespaces
    • CAST in PostgreSQL
    • PostgreSQL CTE
    • hstore in PostgreSQL
    • PostgreSQL Encryption
    • PostgreSQL DECODE()
    • PostgreSQL Vacuum
    • PostgreSQL EXCLUDE
    • Postgres Change Password
    • Postgres Delete Cascade
    • PostgreSQL EXCEPT
    • PostgreSQL Roles
    • PostgreSQL Link
    • PostgreSQL Partition
    • PostgreSQL column does not exist
    • PostgreSQL Log Queries
    • PostgreSQL escape single quote
    • PostgreSQL Query Optimization
    • PostgreSQL Character Varying
    • PostgreSQL Transaction
    • PostgreSQL Extensions
    • PostgreSQL Import CSV
    • PostgreSQL Client
    • PostgreSQL caching
    • PostgreSQL Incremental Backup
    • PostgreSQL JSON vs JSONNB
    • PostgreSQL JDBC Driver
    • PostgreSQL Interview Questions
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Special Offer - All in One Data Science Bundle (360+ Courses, 50+ projects) Learn More