CODEWITHSUNDEEP
postgresql
Tim
Tim

Reputation: 99428

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

  1. 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?

  2. 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?

  3. 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)

Upvotes: 5

Views: 6086

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246848

  1. 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.

  2. 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.

  3. pg_read_all_settings and the others don't show up in pg_user because they are not login roles.

Upvotes: 5

Haleemur Ali
Haleemur Ali

Reputation: 28283

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.

Upvotes: 2

Related Questions