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.


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?


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 it stumbles upon a database it has no connect privs, I had to modify the source a bit. And despite numerous bug reports, it's been doing so for at least six months now (I can't remember any date of the reports, but one of them stated that six months had passed).

  • 1
    I think that everything in new database (including rights) is inherited from database template1.
    – pavel242
    May 30, 2013 at 2:25
  • @pavel242, see dezso answer below, to see that's not true... Unfortunately. :(
    – Michal Rus
    May 30, 2013 at 9:39

2 Answers 2


This is currently not supported in PostgreSQL out of the box. Perhaps you should send in a feature request (or a patch).


The CONNECT privilege for PUBLIC looks somewhat special:

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:


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();
 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.

  • Yes, and that's exactly the behavior I want to avoid. I'd like your user test not to be able to connect to any newly created database, including your test3.
    – Michal Rus
    May 30, 2013 at 9:38
  • @MichałRus Please see my edit above.
    – dezso
    May 30, 2013 at 9:56
  • +1 for this check. :) Well. I've just posted to pgsql-admin, perhaps they'll tell us.
    – Michal Rus
    May 30, 2013 at 10:31
  • (Obviously, I'll be happy to accept your answer if it is really impossible to do so, let's wait for the "official" statement).
    – Michal Rus
    May 30, 2013 at 10:33
  • 1
    @MichałRus the official answer arrived from Peter.
    – dezso
    May 30, 2013 at 13:06

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.