Thread: Access rights for tablespaces, phpPgAdmin versus PgAdmin

I'm coming from MySQL and still trying to get my head around the PostgreSQL
approach to authentication and access control.

I got postgres installed on CentOS 5 with yum, started the server (which
creates the initial database), and installed phpPgAdmin on the same server
under Apache. I then installed PgAdmin 1.8.0 on my XP box on my LAN. Using
psql, I created a login role with all rights except superuser.

I got my head around pg_hba.conf so I can log into both phpPgAdmin and
PgAdmin from my XP system using my new role account.

I then proceeded to create a new database in which to copy existing tables
from another (Windows-based) application. (I plan to use the ODBC
connector.) (As I type this, I just tried this with phpPgAdmin and it
worked. I'll continue, though....) PgAdmin refuses to let me create the new
DB, telling me "permission denied for tablespace pg_default". Some googling
suggests that this is intentional, that if one wants to create a new table
one needs to change the default tablespace permissions to allow it, or
create a new tablespace.

So why does phpPgAdmin succeed, and PgAdmin fail? What's the correct
procedure to follow here? (I don't need a separate tablespace at this time,
although I don't have a problem with creating one.) Do I need to change
permissions on pg_default, and if so, how? I don't see where I can change
permissions on a tablespace in PgAdmin.

Kenneth Porter <shiva@sewingwitch.com> writes:
> I then proceeded to create a new database in which to copy existing tables
> from another (Windows-based) application. (I plan to use the ODBC
> connector.) (As I type this, I just tried this with phpPgAdmin and it
> worked. I'll continue, though....) PgAdmin refuses to let me create the new
> DB, telling me "permission denied for tablespace pg_default".

Are you trying to explicitly specify the tablespace?  If you just let it
default, it should work --- but if you name a tablespace you need CREATE
permission on that tablespace, even if it happens to be the same one
that would be the default choice.

If PgAdmin is trying to specify a tablespace even when you didn't ask
it to, that'd be a bug in PgAdmin IMHO.

            regards, tom lane

--On Thursday, November 15, 2007 12:50 AM -0500 Tom Lane
<tgl@sss.pgh.pa.us> wrote:

> If PgAdmin is trying to specify a tablespace even when you didn't ask
> it to, that'd be a bug in PgAdmin IMHO.

That's possible. It was somewhat ambiguous in the messages I saw in the
various mailing lists, but there seemed to be an indication that the create
code was erroneously not providing a blank option to use the implied
default. I think it may be fixed in a beta but no Windows binary of that
fixed code is yet available on the website.