Thread: CREATE DATABASE impossible if pgAdmin III is connected to the DB
Hi pgAmind hackers!
I have a bug to report here:
I. Environment:
1. Two Windows XP SP2 machines, one is running PgSQL server, and the
other is the "workstation" on which I'm working and seeing the bug.
Both machines are in Wireless LAN, but I doubt it matters. Both have
firewall, but it also does not matter for this case :).
1.1. PgAdmin is working perfectly from my machine, no problems seen so far.
1.2. PgAdmin III 1.3.0 / 01.04.2005
1.3. Npgsql library for connecting .NET test application with the
given server, with small test application, which does:
// connecting to the DB
conn = new NpgsqlConnection("Server=192.168.1.7;Port=5432;User
Id=pgnet;Password=pgnetp;Database=test;");
// opens fine, both user pgnet and database "test" exist
conn->Open();
// Execute DB creation command
NpgsqlCommand * cmd = conn->CreateCommand();cmd->CommandText = "CREATE
DATABASE \"pgnet1\" WITH OWNER = test ENCODING = 'WIN' TABLESPACE =
pg_default;";
cmd->ExecuteReader(); // <== the error appears HERE, look for conditions below
2. The error is:
If I have pgAdmin started and _connected_ to the server, then the command
"CREATE DATABASE \"pgnet1\" WITH OWNER = test ENCODING = 'WIN'
TABLESPACE = pg_default;"
issued by _anyone_else_ ends up with error message
"ERROR: 42501: must be superuser to create database for another user"
PgAdmin III can create Databases without a problem!
3. Resolution: the only resolution found for now is to close pgAdmin III.
If I close pgAdmin III, the command from above succeeds!
If I open it again - the command fails with the same error message.
The conclusion for me is: this is a bug in PgAdmin III. And I suppose
it should be fixed, because I think no database can be created, if at
least one user is connected to the server, and another one is trying
to create a DB in the same time.
I am ready to assist, if needed!
Best regards,
--
Doncho
http://blog.doncho.net
> -----Original Message----- > From: pgadmin-support-owner@postgresql.org > [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of > Doncho Angelov > Sent: 23 April 2005 18:28 > To: pgadmin-support@postgresql.org > Subject: [pgadmin-support] CREATE DATABASE impossible if > pgAdmin III is connected to the DB > > > If I have pgAdmin started and _connected_ to the server, then > the command > "CREATE DATABASE \"pgnet1\" WITH OWNER = test ENCODING = 'WIN' > TABLESPACE = pg_default;" > issued by _anyone_else_ ends up with error message > "ERROR: 42501: must be superuser to create database for another user" Right - this is PostgreSQL complaining as only superusers can create databases with owners other than themselves > PgAdmin III can create Databases without a problem! Presumably you are logging into pgAdmin as a superuser though. All it does is execute the CREATE DATABASE as you have (though through C++/libpq rather than C#/Npgsql). PostgreSQL doesn't behave any differently for pgAdmin over any other application. > 3. Resolution: the only resolution found for now is to close > pgAdmin III. > If I close pgAdmin III, the command from above succeeds! > If I open it again - the command fails with the same error message. > > The conclusion for me is: this is a bug in PgAdmin III. And I suppose > it should be fixed, because I think no database can be created, if at > least one user is connected to the server, and another one is trying > to create a DB in the same time. This sounds like another known issue which results in the following error: template1=# create database baa; ERROR: source database "template1" is being accessed by other users Because pgAdmin connects to template1 initially by default, other users will not be able to create new databases using template1 as the template (which is also the default). There's nothing that can be done about this, except to create the new database from a different template, or select a different initial database for pgAdmin to connect to. Or exit pgAdmin as you've found! Regards, Dave.