12

I installed a default installation of PostgreSQL 8.4 on Windows 2003 Server, using the one-click installer provided. Running psql -l for the first time, I noticed there are three databases installed by default: postgres, template0, and template1.

Being security-minded, my initial reaction is to delete or change default configurations. However, I also know I haven't a clue regarding databases (as this install is my first step in self-learning about databases), so I thought I would ask first.

Is it safe to delete these?

Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
romandas
  • 4,086
  • 7
  • 29
  • 33

2 Answers2

12

Basically - no.

postgres database is here as a non-template database with reasonable guarantee that it exists - so any script that doesn't know where to connect to, can connect there.

if you will remove template1 - you will lose the ability to create new databases (at least easily).

template0 is there as a backup, in case your template1 got damaged.

While I can theoretically imagine a working database with no template* and postgres databases, the thing that bugs me is that i have no idea what (security-wise) you want to achieve by removing them.

  • 1
    It isn't necessarily a wish to remove them if they are necessary -- often things (not Postgres necessarily) are installed by default that are unnecessary and insecure, such as many of the services enabled by default in Windows XP for example. Or older MS SQL installs with blank 'sa' passwords. I wanted to know if the 3 installed by default are necessary -- it seems like having at least template1 is necessary, and template0 is nice to have as a backup, but that postgres seems to be superfluous. Is this correct? – romandas Jul 17 '09 at 01:03
  • 1
    If none of your programs assume existence of postgres database - you can remove it. I wouldn't remove template[01] for a lot of reasons. But then again - I wouldn't also bother to remove postgres database. What good does it do to you (removal)? What vector of attack will be impossible without this database? –  Jul 17 '09 at 12:50
  • 2
    The question should be 'What operational need does keeping the postgres database address?' If there isn't one, you shouldn't have it. Minimize, minimize, minimize. Just because I cannot think of an attack against it doesn't mean there isn't one; after all, I'm doing this install specifically to learn since I'm completely inexperienced with databases. – romandas Jul 17 '09 at 21:55
  • If anyone is wondering what can go wrong if you delete the `postgres` database: when I tried the command `deleteuser` got this error message: `dropuser: could not connect to database postgres: FATAL: database "postgres" does not exist`. It worked after I recreated an empty `postgres` database but apparently it is used for something. – Max Nov 24 '11 at 17:28
  • The commandline add/drop commands are convenience functions that wrap "[CREATE|DROP] [USER|DATABASE|LANGUAGE]" sql commands. If you have _any_ db to connect to as a db superuser, you can issue the appropriate commands – reedstrm Mar 26 '13 at 16:43
6

You can delete the postgres but do not touch template0 or template1. The postgres database is there for convenience.