Database named "postgres"

Question

I've just set up Postgres for use by different users on my network. Every user has his own username/password/database, but when I connect to Pg I can also see a 'postgres' database (and even create tables etc). I tried to REVOKE access to that database from public but then it won't let me connect. What exactly is the postgres database and why is it needed? Can I disable it so that users only see the database(s) I've created for them?

Answer 1

If you drop the postgres database you'll find a few things to be confusing. Most tools default to using it as the default database to connect to, for one thing. Also, anything run under the postgres user will by default expect to connect to the postgres database.

Rather than dropping it, REVOKE the default connect right to it.

REVOKE connect ON DATABASE postgres FROM public;

The superuser (usually postgres), and any users you explicitly grant rights to access the database can still use it as a convenience DB to connect to. But others can't.

To grant connect rights to a user, simply:

GRANT connect ON DATABASE postgres TO myuser;

Answer 2

The postgres database is created by default when you run initdb.

Quote from the manual:

Creating a database cluster consists of creating the directories in which the database data will live (...) creating the template1 and postgres databases. When you later create a new database, everything in the template1 database is copied. (...) The postgres database is a default database meant for use by users, utilities and third party applications.

There is nothing special about it, and if you don't need it, you can drop it:

drop database postgres;

You need to do that as a superuser of course. The only downside of this is that when you run psql as the postgres operating system user, you need to explicitly provide a database name to connect to