Re: BUG #4980: Dblink issue

The following bug has been logged online:

Bug reference: 4980
Logged by: Gaurav K Srivastava
Email address: gauravgkp(at)gmail(dot)com
PostgreSQL version: 8.3
Operating system: Windows xp
Description: Dblink issue
Details:

I have created a user by command

1: create user posuser with password ‘pos’;
2: create database chserver with owner = abc;
3: GRANT ALL PRIVILEGES ON database chserver TO posuser

Now I have a schema under it named 'pos'

Now there is a table 'tickets' ie pos.tickets

Now I want to make a trigger which link another database say it is chclient
using dblink while inserting any row in tickets table.

then i got dblink function is undefined.

after running contrib/dblink.sql
i got the error while using dblink
is
ERROR: permission denied for language c

and when I run the command

ALTER PROCEDURAL LANGUAGE C OWNER TO posuser;

then also I got the same error.

Please tell me how i make the dblink function available to posuser which is
non super user.

On Wed, Aug 12, 2009 at 8:00 AM, Gaurav K Srivastava<gauravgkp(at)gmail(dot)com> wrote:
>
> The following bug has been logged online:
>
> Bug reference:      4980
> Logged by:          Gaurav K Srivastava
> Email address:      gauravgkp(at)gmail(dot)com
> PostgreSQL version: 8.3
> Operating system:   Windows xp
> Description:        Dblink issue
> Details:
>
> I have created a user by command
>
>
> 1:      create user posuser with password ‘pos’;
> 2:      create database chserver with owner = abc;
> 3:      GRANT ALL PRIVILEGES ON database chserver TO posuser
>
>
> Now I have a schema under it named 'pos'
>
> Now there is a table 'tickets' ie pos.tickets
>
> Now I want to make a trigger which link another database say it is chclient
> using dblink while inserting any row in tickets table.
>
> then i got dblink function is undefined.
>
> after running contrib/dblink.sql
> i got the error while using  dblink
> is
> ERROR: permission denied for language c
>
> and when I run the command
>
> ALTER PROCEDURAL LANGUAGE C OWNER TO posuser;
>
> then also I got the same error.
>
> Please tell me how i make the dblink function available to posuser which is
> non super user.

http://www.postgresql.org/docs/current/static/sql-grant.html

I suspect you want some variant of "GRANT USAGE ON LANGUAGE ...".

...Robert

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Wed, Aug 12, 2009 at 8:00 AM, Gaurav K Srivastava<gauravgkp(at)gmail(dot)com> wrote:
>> Please tell me how i make the dblink function available to posuser which is
>> non super user.

> I suspect you want some variant of "GRANT USAGE ON LANGUAGE ...".

C-language functions have to be created by a superuser. They might be
*used* by a non superuser afterwards, but you have to be superuser to
run the "dblink.sql" script. I'm fairly sure that GRANT will not
override this. Since anyone with the power to load arbitrary code into
the backend could make himself a superuser pretty easily, it's a bit
foolish to imagine that granting this privilege to non-superusers
is a good idea.

regards, tom lane