Postgres Default User

Updated May 18, 2023

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.

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: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 –

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

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:

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.

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

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.

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 –

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 –

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 –

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 –

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.

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()