Thread: problem with dblink and "drop owned by"

problem with dblink and "drop owned by"

From
"James W. Wilson"
Date:
Hi, I'm using Postgresql 8.4.12-1 on Debian. I've installed 'dblink'
from the contrib package (also 8.4.12-1) and am now running into a
strange problem when I run "drop owned by".

First, as the superuser I run 'dblink.sql' against the database I'll
be using. Then I connect and do the following (this is against a new
empty database 'foo'):

foo=# create user somelocaluser with password 'somelocaluser';
CREATE ROLE
foo=# create foreign data wrapper postgresql validator postgresql_fdw_validator;
CREATE FOREIGN DATA WRAPPER
foo=# grant usage on foreign data wrapper postgresql to somelocaluser;
GRANT
foo=# grant execute on function dblink_connect(text,text) to somelocaluser;
GRANT
foo=# \c foo somelocaluser
Password for user somelocaluser:
psql (8.4.12)
You are now connected to database "foo" as user "somelocaluser".
foo=> drop owned by somelocaluser;
WARNING:  no privileges could be revoked for "dblink_connect"
ERROR:  unexpected object type 2328

Dropping the foreign data wrapper as the superuser does work, but is
highly inconvenient because I need to be able to run the "drop owned
by" as a non-privileged user as part of an integration testing
procedure. If I don't create the foreign data wrapper, I can do "drop
owned by somelocaluser" while connected as the non-privileged user.

James

Re: problem with dblink and "drop owned by"

From
Tom Lane
Date:
"James W. Wilson" <jww1066@gmail.com> writes:
> Hi, I'm using Postgresql 8.4.12-1 on Debian. I've installed 'dblink'
> from the contrib package (also 8.4.12-1) and am now running into a
> strange problem when I run "drop owned by".

You're running into a old bug, which is that DROP OWNED BY doesn't know
what to do with foreign data wrappers.  According to the commit logs,
this was fixed a couple of years ago in 9.0 and up, but we did not
bother to fix 8.4 because foreign data wrappers aren't actually useful
for anything in 8.4.  If you want to work with FDWs, I'd suggest moving
forward to a release where they have some real functionality ...

            regards, tom lane

Re: problem with dblink and "drop owned by"

From
"James W. Wilson"
Date:
I'm confused. I thought foreign data wrappers were required to create
database links from one Postgresql server to another. Is there some
way to create a database link without using them? I was working off of
this example:

http://www.postgresql.org/docs/8.4/static/contrib-dblink-connect.html

I'm stuck with 8.4 for now, unfortunately.

James

On Wed, Jul 18, 2012 at 6:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "James W. Wilson" <jww1066@gmail.com> writes:
>> Hi, I'm using Postgresql 8.4.12-1 on Debian. I've installed 'dblink'
>> from the contrib package (also 8.4.12-1) and am now running into a
>> strange problem when I run "drop owned by".
>
> You're running into a old bug, which is that DROP OWNED BY doesn't know
> what to do with foreign data wrappers.  According to the commit logs,
> this was fixed a couple of years ago in 9.0 and up, but we did not
> bother to fix 8.4 because foreign data wrappers aren't actually useful
> for anything in 8.4.  If you want to work with FDWs, I'd suggest moving
> forward to a release where they have some real functionality ...
>
>                         regards, tom lane

Re: problem with dblink and "drop owned by"

From
Tom Lane
Date:
"James W. Wilson" <jww1066@gmail.com> writes:
> I'm confused. I thought foreign data wrappers were required to create
> database links from one Postgresql server to another.

contrib/dblink doesn't require them.  It does have an option to use an
FDW instead of a libpq connection string to specify the connection
target.  I had forgotten that that option got added in 8.4, before we
had any other working use for FDWs --- so maybe we should rethink the
decision not to backport this fix?

Author: Heikki Linnakangas <heikki.linnakangas@iki.fi>
Branch: master Release: REL9_1_BR [e356743f3] 2010-11-12 15:29:23 +0200
Branch: REL9_0_STABLE Release: REL9_0_2 [533073cf2] 2010-11-12 15:30:19 +0200

    Add missing support for removing foreign data wrapper / server privileges
    belonging to a user at DROP OWNED BY. Foreign data wrappers and servers
    don't do anything useful yet, which is why no-one has noticed, but since we
    have them, seems prudent to fix this. Per report from Chetan Suttraway.
    Backpatch to 9.0, 8.4 has the same problem but this patch didn't apply
    there so I'm not going to bother.

In the meantime, though, you do not *need* an FDW.  dblink worked fine
for many releases before FDWs existed, and it still does work without
'em.

            regards, tom lane

Re: problem with dblink and "drop owned by"

From
"James W. Wilson"
Date:
OK, understood, thanks.

On Wed, Jul 18, 2012 at 10:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "James W. Wilson" <jww1066@gmail.com> writes:
>> I'm confused. I thought foreign data wrappers were required to create
>> database links from one Postgresql server to another.
>
> contrib/dblink doesn't require them.  It does have an option to use an
> FDW instead of a libpq connection string to specify the connection
> target.  I had forgotten that that option got added in 8.4, before we
> had any other working use for FDWs --- so maybe we should rethink the
> decision not to backport this fix?
>
> Author: Heikki Linnakangas <heikki.linnakangas@iki.fi>
> Branch: master Release: REL9_1_BR [e356743f3] 2010-11-12 15:29:23 +0200
> Branch: REL9_0_STABLE Release: REL9_0_2 [533073cf2] 2010-11-12 15:30:19 +0200
>
>     Add missing support for removing foreign data wrapper / server privileges
>     belonging to a user at DROP OWNED BY. Foreign data wrappers and servers
>     don't do anything useful yet, which is why no-one has noticed, but since we
>     have them, seems prudent to fix this. Per report from Chetan Suttraway.
>     Backpatch to 9.0, 8.4 has the same problem but this patch didn't apply
>     there so I'm not going to bother.
>
> In the meantime, though, you do not *need* an FDW.  dblink worked fine
> for many releases before FDWs existed, and it still does work without
> 'em.
>
>                         regards, tom lane