PostgreSQL Permissions – Do All Users Need Access in pg_hba.conf?

permissionspgadminpostgresql

I was just testing the user creation process in pgAdmin. I have a database db1 for which I want to create a user to be able to connect to it. I want this to be the only database the user can connect to.

After creating the user, I opened my pg_hba.conf file and added a couple of lines:

host    db1 dbuser  127.0.0.1/32    md5
host    db1 dbuser  ::1/128         md5

The postgres user has access to all the databases:

host    all postgres    127.0.0.1/32    md5
host    all postgres    ::1/128         md5

When I connect to the server through pgAdmin, I get an error indicating that dbuser doesn't have access to the postgres database.

Is this normal? Should dbuser even require access to the postgres database, when I want him to only access db1? As soon as I add postgres to the list of databases for dbuser, I can connect.

Best Answer

What you see is pgAdmin connecting to the configured Maintenance DB - which is postgres by default. Select a server in the object browser pane and choose "Properties" from the context menu (right-click). The drop-down menu only offers the typical choices, but you can just type in any database name.

If a user's access is limited to one database in pg_hba.conf (which is the most efficient place to do that), you can make it work with pgAdmin by configuring this one database as maintenance db. Nothing bad will come of it.

The other option is to grant access to the maintenance DB (postgres or whatever) in pg_hba.conf additionally.

You can also open all gates in pg_hba.conf and regulate access with database permissions, like @a_horse explained in his answer. This is easier to manage, but restricting access in pg_hba.conf is safer and faster - more robust against DoS attacks and heavy load. Won't make a big difference in most cases, though.

Quoting the documentation of pgAdmin 1.22 (final release of pgAdmin III):

The maintenance DB field is used to specify the initial database that pgAdmin connects to, and that will be expected to have the pgAgent schema and adminpack objects installed (both optional). On PostgreSQL 8.1 and above, the maintenance DB is normally called 'postgres', and on earlier versions 'template1' is often used, though it is preferable to create a 'postgres' database for this purpose to avoid cluttering the template database.

Related Solutions

Postgresql – Howto disable Postgres listening on TCP

Maybe it is not the finest Solution / Answer to my question, but at least it will point anybody (facing the said challenge I had in my original question)

To disable listening on TCP/IP network I used this command line option when starting the server application:

postgres [other arguments] -c listen_addresses=''

Addition: The remaining open udp 127.0.0.1:38860 connection is supposedly linked to the purpose of the the statistics collector subprocess as suggested on postgresql.org

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 – Howto disable Postgres listening on TCP

Postgresql – Authentification failure for PostgreSQL server (9.3)

Related Question