Is "postgres" a default and special user of PostgreSQL?

Question

Chapter 21. Database Roles lists the default roles of PostgreSQL. But I don't find user postgres there, which has been created by default in PostgreSQL. Is postgres a default role? Does the manual miss it or do I misunderstand?
In PostgreSQL, is postgres a special user, or a regular user just like one created manually? Does the PostgreSQL server need the user postgres? Will removing it cause some trouble to the server or something else?

The following two commands run in psql provide default roles or usernames, which both include postgres. Why do they differ?

# select usename from pg_catalog.pg_user;

 usename
----------
 postgres
(1 row)

# select rolname from pg_catalog.pg_roles;

       rolname
----------------------
 postgres
 pg_monitor
 pg_read_all_settings
 pg_read_all_stats
 pg_stat_scan_tables
 pg_signal_backend
(6 rows)

This page is reproduced without any attribution at techhelpnotes DOT com/is-postgres-a-default-and-special-user-of-postgresql/ (that is, plagiarised). — Peter Mortensen
– Peter Mortensen, Commented Sep 12, 2022 at 21:16

Peter Mortensen · Accepted Answer · 2022-09-14 10:16:05Z

6

postgres is not a default role.

When you create the PostgreSQL database cluster with initdb, you can specify the name of the installation superuser with the -U option. If you omit that option, the name of the superuser will be the same as the name of the operating system user you are using.

Since it is customary to have initdb PostgreSQL run by an operating system user postgres, the superuser is usually called postgres too, but that isn't in any way required.
postgres is just a normal superuser like any other.

You will have trouble dropping it because it owns all the system objects, and you cannot easily modify those objects. You are advised not to try.
pg_read_all_settings and the others don't show up in pg_user because they are not login roles.

edited Sep 14, 2022 at 10:16

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Jun 16, 2018 at 3:39

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Tim Over a year ago

Thanks. 1. " If you omit that option, the name of the superuser will be the same as the name of the operating system user you are using." I have never created a user "postgres" on my OS Lubuntu 18.04, so how can "postgres" be used as the name of the superuser? 3. " they are no login roles." Is it correct that login roles are exactly users and nonlogin roles are exactly groups?

Tim Over a year ago

1. On LUbuntu 18.04, I installed postgresql by sudo apt install postgresql

Stephen Frost Over a year ago

Installing the package on Ubuntu automatically creates a Unix user "postgres". You can see that user in /etc/passwd.

Laurenz Albe Over a year ago

@Tim Yes, a user in PostgreSQL is the same as a role that can login. Normally nologin roles are used as groups, but that is not a requirement.

Tim Over a year ago

Thanks. "nologin roles are used as groups, but that is not a requirement." Do you mean a nonlogin role may still be a user not a group?

|

Haleemur Ali · Accepted Answer · 2018-06-15 23:40:42Z

2

postgres is the first user that is available after an installation. it is a super user. But, it is possible to define your own super users which will have equivalent permissions to the postgres user.

A user is a role that has the ability to log in.

Roles without login privilege are used for various system level uses and are sometimes also used to manage access control rules through inheritance (e.g. you may have a role analysts and a user hal that is granted membership to the analysts role)

Thus pg_user only returns those roles that are able to log into the database.

answered Jun 15, 2018 at 23:40

Haleemur Ali

28.6k6 gold badges67 silver badges89 bronze badges

2 Comments

Tim Over a year ago

Thanks. (1) why is postgres not listed as default role in the link? (2) Is it correct that a user == a role which can log in, and, a group == a role which can't log in? (3) Does the PostgreSQL server need the user postgres? WIll removing it cause some trouble to the server or something else? (4) Is there a command to show whether a user such as postgres is a superuser, and a command which can show all the superusers?

Haleemur Ali Over a year ago

1 - see other answer from laurenz. 2 - yes. 3 - no, but deleting is tricky and you have to transfer all objects owned by postgres to other user(s). 4 - select rolname from pg_catalog.pg_roles where rolsuper will show all roles that are super

Collectives™ on Stack Overflow

Is "postgres" a default and special user of PostgreSQL?

2 Answers 2

7 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related