When working with a PostgreSQL instance, most of the time the initial superuser is called “postgres”. One might think this is always the case, and this user is there by default. But the name for that user is chosen by some rules and in this post we’ll look at this.
In almost all the installations we’ve seen out there, PostgreSQL is running under the “postgres” operating system user. On my sandbox this is true as well:
postgres@debian12-pg:/home/postgres/ [pg170] whoami
postgres
When you initialize a new cluster with that user, you by default get the “postgres” superuser in the new cluster:
postgres@debian12-pg:/home/postgres/ [pg170] initdb --pgdata /var/tmp/xx
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/tmp/xx ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Europe/Zurich
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /var/tmp/xx -l logfile start
Starting up the cluster and checking for the user name confirms this:
postgres@debian12-pg:/home/postgres/ [pg170] export PGPORT=9999
postgres@debian12-pg:/home/postgres/ [pg170] pg_ctl --pgdata /var/tmp/xx start
postgres@debian12-pg:/home/postgres/ [pg170] psql -h /tmp -c "\du"
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
Initializing a cluster with another operating system user will give that user name as the initial superuser name:
postgres@debian12-pg:/home/postgres/ [pg170] sudo useradd -s /bin/bash daniel
postgres@debian12-pg:/home/postgres/ [pg170] sudo su - daniel
daniel@debian12-pg:/home/postgres$ /u01/app/postgres/product/DEV/db_0/bin/initdb -D /var/tmp/yy
The files belonging to this database system will be owned by user "daniel".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
creating directory /var/tmp/yy ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Europe/Zurich
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/u01/app/postgres/product/DEV/db_0/bin/pg_ctl -D /var/tmp/yy -l logfile start
Once more, starting up this cluster and checking for the superuser’s username confirms this:
daniel@debian12-pg:/home/postgres$ export PGPORT=4444
daniel@debian12-pg:/home/postgres$ /u01/app/postgres/product/DEV/db_0/bin/pg_ctl -D /var/tmp/yy start
daniel@debian12-pg:/home/postgres$ /u01/app/postgres/product/DEV/db_0/bin/psql -h /tmp -c "\du" postgres
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
daniel | Superuser, Create role, Create DB, Replication, Bypass RLS
Another option you have to specify the name for the initial superuser is, to tell initdb how you want to name it:
postgres@debian12-pg:/home/postgres/ [pg170] whoami
postgres
postgres@debian12-pg:/home/postgres/ [pg170] initdb --help | grep username
-U, --username=NAME database superuser name
By using this flag you are free to choose the name for the initial superuser:
postgres@debian12-pg:/home/postgres/ [pg170] initdb --pgdata /var/tmp/zz --username=debian
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
creating directory /var/tmp/zz … ok
creating subdirectories … ok
selecting dynamic shared memory implementation … posix
selecting default max_connections … 100
selecting default shared_buffers … 128MB
selecting default time zone … Europe/Zurich
creating configuration files … ok
running bootstrap script … ok
performing post-bootstrap initialization … ok
syncing data to disk … ok
initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /var/tmp/zz -l logfile start
postgres@debian12-pg:/home/postgres/ [pg170] export PGPORT=3333
postgres@debian12-pg:/home/postgres/ [pg170] pg_ctl --pgdata /var/tmp/zz/ start
postgres@debian12-pg:/home/postgres/ [pg170] psql -h /tmp -c "\du" -U debian postgres
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
debian | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
To summarize: By default you get the same user name as the operating system user you use for initializing the cluster. If you want to change it, then tell initdb how you would like the user to be called.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)