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