Thread: problem with dblink and "drop owned by"
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
"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
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
"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
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