Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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 grant access to the maintenance 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.

I quote the help of pgAdmin:

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.

share|improve this answer
    
This is exactly what I was looking for. To me it makes a whole lot more sense to set up the user config in the pg_hba.conf file since its like a "first line of defense". The fact that the maitenance DB field comes prefilled is misleading. –  MytyMyky Jun 13 '12 at 9:16

You don't need to do this through the pg_hba.conf.

Simply revoke the connect privilege on the database from that user.

However by default public is granted the connect privilege. So you need to first revoke that:

revoke connect on db1 from public;
grant connect on db1 to dbuser;

You need to run the revoke ... from public statement for all databases to make sure only the specific users can connect.

Once you have done that, you can leave pg_hba.conf "open" for all users and all databases.

share|improve this answer
    
Thanks. I had seen the revoke / grant need in some documentation, but I couldn't wrap my head around the need to add postres in the database list for the user to be able connect. –  MytyMyky Jun 11 '12 at 20:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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