20

I have a PostgreSQL server with several databases running on it. One of the databases is called postgres, and it was there right from the beginning. I could not see any tables inside and I've never used it explicitly, but I noticed, that any user is allowed to create tables within the database (this is not what I want - I do not want any users except the administrator to create any tables anywhere).

I have a feeling that it is some kind of a special database that is required for a normal operation of the server, but what is exactly its purpose? What kind of data is (or could be) stored inside? Does it hold the metadata about other databases (e.g. the information_schema?). Can something nasty happen, if I revoke connect privilege to this database from public? Or if I even drop it?

I googled for half an hour, but always got completely unrelated stuff, because the word postgres is also used for the user postgres and the DBMS itself.

Roman Kiselev
  • 303
  • 2
  • 6

1 Answers1

31

There is nothing special about it. As described in PostgreSQL documentation, Creating a Database, you need to be connected to a database, in order to create another one:

Since you need to be connected to the database server in order to execute the CREATE DATABASE command, the question remains how the first database at any given site can be created. The first database is always created by the initdb command when the data storage area is initialized. (See Section 17.2.) This database is called postgres. So to create the first "ordinary" database you can connect to postgres.

and in Template Databases:

Note: template1 and template0 do not have any special status beyond the fact that the name template1 is the default source database name for CREATE DATABASE. For example, one could drop template1 and recreate it from template0 without any ill effects. This course of action might be advisable if one has carelessly added a bunch of junk in template1. (To delete template1, it must have pg_database.datistemplate = false.)

The postgres database is also created when a database cluster is initialized. This database is meant as a default database for users and applications to connect to. It is simply a copy of template1 and can be dropped and recreated if necessary.

Community
  • 1
ypercubeᵀᴹ
  • 97,895
  • 13
  • 214
  • 305
  • 4
    Yep. In practice you should generally leave it empty and unchanged as a handy place to connect to. This is really a wart in PostgreSQL, a consequence of the fact that PostgreSQL doesn't let you connect to "no database in particular" like other DBMSes do. – Craig Ringer Jul 19 '16 at 09:10
  • 1
    Thanks a lot, this is a good answer! I denied access of public users to postgres and everything still works, except one thing: pgAdmin III does not connect anymore unless I specify the targed database in the Maintenance DB field. It seems that it used to connect to postgres database before. – Roman Kiselev Jul 19 '16 at 10:04
  • @RKiselev Yeah, it works like that. Just like ypercube wrote, it's default database to connect to. – Marcin S. Jul 19 '16 at 13:58
  • 1
    Pgadmin has "maintenance database" property. Just change it to your db that you want to connect – Sahap Asci Jul 19 '16 at 23:29
  • @RKiselev: About pgAdmin and its "maintenance database": What is the Maintenance DB asked by pgAdmin? – Erwin Brandstetter Jul 22 '16 at 17:55