71

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.

Improve this question
1
  • 1
    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
    Commented May 13, 2012 at 13:55

4 Answers 4

Reset to default
77

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.

Improve this answer
1
  • 4
    The default should have been the other way around. I want to create a user with a randomly generated password and grant it access to a single DB, knowing that postgres can access all databases.
    – TheRealChx101
    Commented Jul 26, 2019 at 3:32
34

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;
Improve this answer
6
  • 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
    Commented May 13, 2012 at 13:25
  • 1
    You're more than welcome as a newcomer, also to dispute settings. Everybody can learn from that!
    – Frank Heikens
    Commented May 13, 2012 at 13:55
  • 1
    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é
    Commented May 13, 2012 at 17:46
  • 2
    @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
    Commented May 13, 2012 at 19:57
  • 1
    Yes. I'm adding related links to my answer, you may want to read a couple more docs about that.
    – Daniel Vérité
    Commented May 14, 2012 at 10:11
4

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

Improve this answer
2
  • 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
    Commented May 13, 2012 at 13:29
  • 1
    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
    Commented May 13, 2012 at 13:53
0

I came across this thread looking for a way to prevent users from even listing the other database names. The REVOKE CONNECT does not prevent this.

As per the answers to this SO question there isn't a (recommendable) way to achieve it.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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