Change built-in default privileges in PostgreSQL?

Introduction.

When I create a database,

postgres=# CREATE DATABASE test2 OWNER test2;

it is created with an empty privileges column:

Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 test2     | test2    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

Now, it is important to note that this is not the same situation as it having no privileges granted whatsoever. In the latter case, the column contains just {} (which by the way displays as empty, too, in postgres=# \l, AFAIR).

• An empty field means that PostgreSQL uses built-in default privileges.

• {} means that there are literally no privileges to the database.

Research.

In our case, these built-in defaults happen to be:

=Tc/test2  +  test2=CTc/test2

How do I know that? I issued:

postgres=# GRANT CONNECT ON DATABASE test2 TO test1;

... and that resulted in:

Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 test2     | test2    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/test2            +
           |          |          |             |             | test2=CTc/test2      +
           |          |          |             |             | test1=c/test2

... that is the implicit defaults plus test1=c/test2 granted explicitly.

Problem & question.

While I have no problem with =CTc granted to owner in these defaults, I'd like not to have =Tc granted to PUBLIC.

Sure, I can just REVOKE ALL [...] FROM PUBLIC after creation, but is there a way to automatize it?

Afterthought.

Really, why aren't these defaults as I want them by default? Is it a popular practice to run one PostgreSQL server for one app, not just one database on the server per app, even if it is a small one?

E.g. default installation of phpPgAdmin "crashes" when i

The CONNECT privilege for PUBLIC looks somewhat special:

\l
template1   | postgres | UTF8     | Hungarian, Hungary | Hungarian, Hungary | postgres=CTc/postgres

That is, user test (which is the member of PUBLIC only) cannot connect to template1:

postgres=# \c template1 test
Password for user test:
FATAL:  permission denied for database "template1"
DETAIL:  User does not have CONNECT privilege.
Previous connection kept

Now let's create a database on top of this:

CREATE DATABASE test3 TEMPLATE template1;

And connect to it with test:

postgres=# \c test3 test
Password for user test:
You are now connected to database "test3" as user "test".
test3=> \c postgres postgres

Just a bit of context:

postgres=# SELECT version();
                           version
-------------------------------------------------------------
 PostgreSQL 9.1.4, compiled by Visual C++ build 1500, 32-bit
(1 row)

With this in mind and the fact that ALTER DEFAULT PRIVILEGES does not provide means the change the defaults on the database level, I would say there is no way to do this automatically. If you have to create lots of databases, probably a setup script would come handy.

StackExchange Database Administrators Q#43387, answer score: 4