In Postgres 11, the CREATE DATABASE command that makes a new catalog has an option ALLOW_CONNECTIONS. The documentation says this option can be used to block all connections to the database:

[ ALLOW_CONNECTIONS [=] allowconn ]


If false then no one can connect to this database. The default is true, allowing connections (except as restricted by other mechanisms, such as GRANT/REVOKE CONNECT).

➥ Why would anyone want to create a database that nobody can connect to?

➥ If you cannot connect, how can you ever change this setting to true to allow connections?


I think you are getting mixed up with the terminology a bit here - in Postgres you have a server (sometimes also called a cluster even if there is only one) which contains many databases. When you connect to the server, you specify which database you want to be in, or none at all in which case you are placed in the default databases which is simply called postgres. So

  1. You can create such a database to hold objects that you don't want anyone to access directly at the present time, such as during an initial setup phase, or to revoke connecting to a database that is scheduled to be decommissioned or archived without actually deleting it yet.
  2. You can always connect as a suitably privileged user to the default database, and on that connection modify the properties of another database

It gives you a chance to revoke connect from unwanted users before doing alter database ... allow connections

Also in all cases users with superuser permission are allowed to connect, so you can create an unconnectable database, then as superuser populate it and create constraints ans indices before making it publicly available.

  • This isn't true: one of the values of ALLOW_CONNECTIONS false is precisely that it also affects superusers and disallows connections from them as well. REVOKE CONNECT does not affect superusers, however. – jcoleman Dec 30 '19 at 17:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.