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.

I must be missing something with regards to setting up PostgreSQL. What I'd like to do is create multiple databases and users that are isolated from each other so that a specific user only has access to the databases I specify. However, from what I can determine, any created user has access to all databases without any specific grants being given.

Here is what I do on an Ubuntu Server 12.04:

  1. apt-get install postgresql
  2. sudo -u postgres createuser -DRSP mike1 (Specifying the password for the new user)
  3. sudo -u postgres createdb data1
  4. psql -h localhost -U mike1 data1 (Specifying the password for the user mike1 to login)

It seems that new user "mike1" has no problem connecting to database "data1" and creating tables etc. And this without running any GRANT command at all (and the owner of "data1" is "postgres" since I didn't specify an owner in step 3). Is this really how it is supposed to work?

What I'd like to do is grant mike1 full access to data1 and then repeat this for more users and databases, making sure that the users only have access to one (or possibly several) databases of my choice.

share|improve this question
    
Keep in mind that even if a user is limited to one database, they can still query the global tables, which will allow them to see the list of database names and the list of users. –  kgrittn May 13 '12 at 13:55

3 Answers 3

up vote 5 down vote accepted

At the SQL level, every user can indeed connect to a newly created database, until the following SQL command is issued:

REVOKE connect ON DATABASE database_name FROM PUBLIC;

Once done, each user or role that should be able to connect has to be granted explicitly the connect privilege:

GRANT connect ON DATABASE database_name TO rolename;

Edit: In a multi-tenant scenario, more than just the connect privilege would be removed. For multi-tenancy tips and best practices, you may want to read on the postgresql public wiki: Shared Database Hosting and Managing rights in PostgreSQL.

share|improve this answer

PUBLIC has access to the database by default, but it can't access the data. You can REVOKE the PUBLIC:

REVOKE CONNECT ON DATABASE your_database FROM PUBLIC;

If you want this setting for all future databases, revoke CONNECT on the template1 database (default template database for creating a new database):

REVOKE CONNECT ON DATABASE template1 FROM PUBLIC;
share|improve this answer
    
I see. Now it makes more sense. I guess I shouldn't come here as a newcomer to PostgreSQL and dispute that maybe PUBLIC should not have CONNECT privilege on template1 as a default :) But I now also see that the data was never in jeopardy. Thanks! –  mikeplate May 13 '12 at 13:25
    
You're more than welcome as a newcomer, also to dispute settings. Everybody can learn from that! –  Frank Heikens May 13 '12 at 13:55
    
Actually, that CONNECT privilege is not passed from the template to the new database so revoking it on template1 doesn't have the mentioned effect. –  Daniel Vérité May 13 '12 at 17:46
    
@DanielVérité I see. So I guess the solution is to always remember and do REVOKE CONNECT when creating a new database. Is this really how it is usually done by PostgreSQL admins, or shouldn't I care that much since the data isn't accessible anyway? Still, I think a list of tables may give away unnecessary information for future attacks, if only between already authorized users in a multi-tenant environment. Also: just realized that public can also create its own tables in any database that hasn't been REVOKE CONNECT. Feels a bit weird to have as a default, I must say. –  mikeplate May 13 '12 at 19:57
    
Yes. I'm adding related links to my answer, you may want to read a couple more docs about that. –  Daniel Vérité May 14 '12 at 10:11

Besides revoking connection privileges from PUBLIC by default, and granting them as specifically desired, the other level at which you can control access is through the pg_hba.conf file.

You can find where the file is stored with:

SHOW hba_file;

If you choose to use this mechanism, there are embedded comments which may be enough to get you started. The docs are here:

http://www.postgresql.org/docs/current/interactive/auth-pg-hba-conf.html

share|improve this answer
    
Thanks! I did look at the pg_hba.conf file but I was under the impression that it only governs how a user authenticates when connecting to a database and not what privileges the user has in that same database. –  mikeplate May 13 '12 at 13:29
    
A user can only connect to databases as allowed by pg_hba.conf. That includes not only the combination of user and database, but also the host from which they're connecting and the method of authentication allowed. If you don't need that granularity of control, the GRANT/REVOKE technique discussed in other answers is probably easier. For one thing, you just need a superuser database connection for that, versus needing an OS login which can edit the file. –  kgrittn May 13 '12 at 13:53

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.