How to specify the superuser of a PostgreSQL database?

Question

I'd like to create a PostgreSQL database and be able to connect as a user which is specified at creation. Therefore I'm using initdb and createdb. However the initdb --user/-U option seems to be ignored:

> cd `mktemp -d`
> /usr/lib/postgresql/9.6/bin/initdb --user=user1 test1
The files belonging to this database system will be owned by user "richter".
This user must also own the server process.

The database cluster will be initialized with locale "de_DE.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "german".

Data page checksums are disabled.

creating directory test1 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
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:

    /usr/lib/postgresql/9.6/bin/pg_ctl -D test1 -l logfile start

The connection with createdb after starting the server with the postgres command fails because user1 isn't available. richter is the user executing the command.

I'm using PostgreSQL 9.6 on Ubuntu 17.04.

Laurenz Albe · Accepted Answer · 2017-08-02 10:55:03Z

1

Two things:

You are mixing up database users and operating system users.

Since you run initdb as OS user richter, that user will own the database files and the database server process.

The user user1 is a database superuser.
You have to specify the database user with the --username or -U option with psql, createdb and other PostgreSQL client applications.

You should show commands and error messages rather than saying “the connection fails”.

answered Aug 2, 2017 at 10:55

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

Kalle Richter Over a year ago

Thank you. I figured that escaping usernames in the long options is difficult - I didn't systemtically investigate all combinations of no, single and double quotes, but using -U without quotes worked fine for me.

Laurenz Albe Over a year ago

One word of advice here: always use only lowercase ASCII characters, numbers and the underscore for database object names (including users). You'll save yourself a lot of trouble, and you never need to quote or escape anything.

Collectives™ on Stack Overflow

How to specify the superuser of a PostgreSQL database?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related