PostgreSQL – automatically create a database for users on first login

postgresql

Is there a way to configure PostgreSQL to create a database whose name is the same as the username on the user's first login?

Scenario:

In pg_hba.conf there will be a rule something like:

local sameuser all ident sameuser

When a user connects to PostgreSQL via console/psql,

If a database with the users name exists, just connect to database.
If it does not exist, create a database named after the user and connect the user to the newly created database.

There are a lot of users and I do not want to manually create these databases all at once, upfront.

Best Answer

No that's not possible.

Even if it was possible: as you need to specify a database when you connect, the new database wouldn't be the one the user connected to (and there is no way to re-route the user to the newly created one).

Related Solutions

Postgresql – postgres 9.1: both md5 and ident on the same connection type

Only if you can differentiate by user or database. You can't have both ident and md5 enabled for the same (user,database,connection-origin) tuple.

I usually use unix sockets with ident for my admin and casual use. Many apps only work over tcp, so I use tcp with md5 for them. For apps that can use a unix socket I specify their database as md5 and just use a password when I connect to it.

It'd be really nice if Pg supported fallback auth where you could try several protocols or where the client could pick, but right now AFAIK it doesn't.

Postgresql – Authentification failure for PostgreSQL server (9.3)

I found a temporary solution to my problem. I edited the pg_hba.conf of the 9.3 server to say trust in the first two active lines. And after restarting (sudo service postgresql restart) I can connect to the server again using pgadmin. The downside is that the server might now not be password protected (I will open a separate question if I encounter any problems with that).

    # Database administrative login by Unix domain socket
    local   all             postgres                                trust

    # TYPE  DATABASE        USER            ADDRESS                 METHOD

    # "local" is for Unix domain socket connections only
    local   all             all                                     trust
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            md5
    # IPv6 local connections:
    host    all             all             ::1/128                 md5
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    #local   replication     postgres                                peer
    #host    replication     postgres        127.0.0.1/32            md5
    #host    replication     postgres        ::1/128                 md5

Best Answer

Related Solutions

Postgresql – postgres 9.1: both md5 and ident on the same connection type

Postgresql – Authentification failure for PostgreSQL server (9.3)

Related Question