Thread: Revoke Public Database Connect

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

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
AFTERthe revoke. 
>
> --- On Fri, 5/27/11, Alan Gutierrez<alan@prettyrobots.com>  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,
thatis 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
createa 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
>

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