I'm having a strange issue with postgres...I think.
I have the following query:
GRANT SELECT ON 'tablename' TO 'username';
As far as I can tell, thats a valid query. It works fine when I run it
under the psql command line interface and executed against a remote db.
When I try and run the same query in pg-Admin from the same machine to
the same remote db, however, the query never terminates. It just runs
forever. I've let it run for about a half hour before. psql returns
almost immediately.
I've run into the same kind of behavior when attempting to execute a
stored procedure from code. The specific instance looked something like
this in C# .NET using the Npgsql data provider:
using (NpgsqlConnection conn = new NpgsqlConnection(connString))
using (NpgsqlCommand command = new pgsqlCommand("Function(:a,:b)", conn))
{
conn.Open();
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("a", DbType.Double).Value = this.a;
command.Parameters.Add("b", DbType.Double).Value = this.b;
result = command.ExecuteScalar();
}
In this case, the user specified in the connection string has SELECT
rights on the appropriate table but did not have EXECUTE rights on the
stored proc. The code would block at 'command.ExecuteScalar();'
forever. Granting execute rights resolved the issue and everything
returned as expected. I've reproduced the same issue using the pgODBC
driver as well. The code looks almost exactly the same.
Has anyone else encountered these issues? In the former case, I don't
understand why the query would fail. Using the explain option from the
title bar (ie. F7) gave a syntax error at a random character in the
query. Usually character 9 (the 'L' in "SELECT") but sometimes a
different one. In the latter case I at least expected postgres to
return some kind of error. Any insights into this would be helpful!
BTW, specs of the machine I'm running postgres on and other details:
Win2k3 Server x64
AMD Opeteron 2GHz
loads of RAM
postgres 8.2
no firewalls of any kind on either box involved
the latter issue was running in a multithreaded/concurrent access
environment but I don't have any issues with queries when the user has
all the necessary rights even under enormous load and I consistently
have issues on the very first query when I would expect a permissions error.
Also, not sure if this fit in the general mailing list or just the sql
mailing list. Sorry if this is off topic in general.
Thanks,
Aroon
