FAQ
I'm configuring a multi-tenant PostgreSQL server. When I create a new
database, anyone can connect to it. For me, that is bad.

I run:

REVOKE CONNECT ON DATABASE d FROM public;

Now I'm only able to connect to the database as postgres.

I tired putting the create and revoke in a transaction, but create
database cannot be put in a transaction. How do I create a database so
there is not that nanosecond window where someone could connect to the
database publiclly?

Search Discussions

  • Francisco Leovey at May 27, 2011 at 11:05 pm
    That is a ridiculous "nanosecond window" - the database you created is empty - nothing to connect to. Just load data AFTER the revoke.

    --- On Fri, 5/27/11, Alan Gutierrez wrote:


    From: Alan Gutierrez <alan@prettyrobots.com>
    Subject: [NOVICE] Revoke Public Database Connect
    To: pgsql-novice@postgresql.org
    Date: Friday, May 27, 2011, 2:55 PM


    I'm configuring a multi-tenant PostgreSQL server. When I create a new database, anyone can connect to it. For me, that is bad.

    I run:

    REVOKE CONNECT ON DATABASE d FROM public;

    Now I'm only able to connect to the database as postgres.

    I tired putting the create and revoke in a transaction, but create database cannot be put in a transaction. How do I create a database so there is not that nanosecond window where someone could connect to the database publiclly?

    --
    Alan Gutierrez - http://twitter.com/bigeasy - http://github.com/bigeasy

    -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-novice
  • Lacey L. Powers at May 27, 2011 at 11:39 pm
    Hello Alan,

    You can also be very careful with your pg_hba.conf settings, and only
    allow certain users connections:

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

    Also, you can create the database with CONNECTION LIMIT 0, and only
    superusers will be allowed to connect to it.

    http://www.postgresql.org/docs/current/static/sql-createdatabase.html

    You could then load the data, and change the connection limit, after
    you've appropriately loaded the data.

    Hope that helps. =)

    Regards,

    Lacey



    That is a ridiculous "nanosecond window" - the database you created is empty - nothing to connect to. Just load data AFTER the revoke.

    --- On Fri, 5/27/11, Alan Gutierrezwrote:


    From: Alan Gutierrez<alan@prettyrobots.com>
    Subject: [NOVICE] Revoke Public Database Connect
    To: pgsql-novice@postgresql.org
    Date: Friday, May 27, 2011, 2:55 PM


    I'm configuring a multi-tenant PostgreSQL server. When I create a new database, anyone can connect to it. For me, that is bad.

    I run:

    REVOKE CONNECT ON DATABASE d FROM public;

    Now I'm only able to connect to the database as postgres.

    I tired putting the create and revoke in a transaction, but create database cannot be put in a transaction. How do I create a database so there is not that nanosecond window where someone could connect to the database publiclly?

    --
    Alan Gutierrez - http://twitter.com/bigeasy - http://github.com/bigeasy

    -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-novice
  • Steve Crawford at May 27, 2011 at 11:43 pm

    On 05/27/2011 10:55 AM, Alan Gutierrez wrote:
    I'm configuring a multi-tenant PostgreSQL server. When I create a new
    database, anyone can connect to it. For me, that is bad.

    I run:

    REVOKE CONNECT ON DATABASE d FROM public;

    Now I'm only able to connect to the database as postgres.

    I tired putting the create and revoke in a transaction, but create
    database cannot be put in a transaction. How do I create a database so
    there is not that nanosecond window where someone could connect to the
    database publiclly?

    --
    Alan Gutierrez - http://twitter.com/bigeasy - http://github.com/bigeasy
    Um, really? Did you set pg_hba.conf to allow such a thing? That would be
    bad.

    If you, the superuser, create a database a normal user shouldn't be able
    to connect to it until you grant them privilege to do so. (Note that
    technically speaking anyone can connect to the database - a TCP or
    socket connection must be established to even pass the initial
    credentials - but PostgreSQL won't let them do anything till they pass
    muster.)

    It sounds to me like you have bad pg_hba.conf settings, have regular
    users who are granted excessive rights, or, perhaps, are creating
    databases from a template that has been altered to have loose permissions.

    IBM Developer Works has a good article on the subject:
    http://www.ibm.com/developerworks/opensource/library/os-postgresecurity/index.html

    Cheers,
    Steve

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedMay 27, '11 at 7:40p
activeMay 27, '11 at 11:43p
posts4
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase