Should I create a database for every user in PostgreSQL?

Question

My question is: when I create a PostgreSQL user different from the default postgres user, should I also create a new database for that user to connect to?

What's the point of a setup like that?

Answer 1

A few explanations:

• Don't use the postgres database for user data. It is intended for administrative purposes, for example as a database to connect to if you want to run CREATE DATABASE.

  This has nothing to do with users.

• Users are cluster-wide, that is, all databases in a cluster share the same users. Note that that does not imply that every user can connect to each database.

• PostgreSQL command line programs have two defaults:

  • If you don't specify a database user, the default is the database user that is called like the operating system user.

  • If you don't specify a database, the default is the database with the same name as the database user.

I assume that it is this last default that inspires your question. This is just a default value and should not influence your database design.

• Create one database for each separate body of data, like all the data that belong to one application.

• Create users as your application and data maintenance procedures require. It is a good idea to use different users for different tasks. For example, the user that performs the backup should not be used by your application to connect to the database.

Answer 2

No. Even if it's a local admin user so you don't need to go through sudo, you should just add export PGDATABASE=postgres to your .bashrc or .profile. I always make a new superuser with the name of my local user, and configure pg_hba.conf to allow local connection if necessary.