Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
I think that everything in new database (including rights) is inherited from database template1. –  pavel242 May 30 '13 at 2:25
    
@pavel242, see dezso answer below, to see that's not true... Unfortunately. :( –  Michał Rus May 30 '13 at 9:39
add comment

2 Answers

up vote 3 down vote accepted

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

share|improve this answer
    
Okay, thank you. :) –  Michał Rus May 30 '13 at 14:01
add comment

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.

share|improve this answer
    
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. –  Michał Rus May 30 '13 at 9:38
    
@MichałRus Please see my edit above. –  dezso May 30 '13 at 9:56
    
+1 for this check. :) Well. I've just posted to pgsql-admin, perhaps they'll tell us. –  Michał Rus May 30 '13 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). –  Michał Rus May 30 '13 at 10:33
1  
@MichałRus the official answer arrived from Peter. –  dezso May 30 '13 at 13:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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