Обсуждение: dblink password required
Hi, I’m trying to use dblink to connect from database1 to database2 on the same machine. The Postgres version is 9.3.5. Firstly, I’ve created a user to use with the connection, while logged in as the postgres superuser to database2: > create user db_link with password ‘mypassword’; > grant select on appuser to db_link; Secondly, the following has been added to pg_hba.conf and a restart done. # dblink between database1 and database2 local database2 db_link md5 host database2 db_link 127.0.0.1/32 md5 host database2 db_link ::1/128 md5 Connecting with PSQL prompts for a password as expected: > psql -U db_link -d database2 Password for user db_link: psql (9.3.5) Type "help" for help. Then, as a normal user logged into database1, I tried > select * from dblink(‘dbname=database2 username=db_link password=mypassword','select username, email from appuser') ast1(username text, email text); However this returns ERROR: password is required DETAIL: Non-superusers must provide a password in the connection string. This is my first time trying dblink so there’s probably something simple I’m missing, but I thought I had provided a password.Any ideas? Regards Oliver Kohll
On 10/24/2014 09:30 AM, Oliver Kohll - Mailing Lists wrote: > Hi, > > I’m trying to use dblink to connect from database1 to database2 on the same machine. The Postgres version is 9.3.5. > > Firstly, I’ve created a user to use with the connection, while logged in as the postgres superuser to database2: > >> create user db_link with password ‘mypassword’; >> grant select on appuser to db_link; > > Secondly, the following has been added to pg_hba.conf and a restart done. > > # dblink between database1 and database2 > local database2 db_link md5 > host database2 db_link 127.0.0.1/32 md5 > host database2 db_link ::1/128 md5 > > Connecting with PSQL prompts for a password as expected: > >> psql -U db_link -d database2 > Password for user db_link: > psql (9.3.5) > Type "help" for help. > > Then, as a normal user logged into database1, I tried > >> select * from dblink(‘dbname=database2 username=db_link password=mypassword','select username, email from appuser') ast1(username text, email text);: I think the problem is the above- ^^^^^^^^^^^^^^^ username=db_link should be user=db_link The accepted keywords are here: http://www.postgresql.org/docs/9.3/static/libpq-connect.html#LIBPQ-CONNSTRING 31.1.2. Parameter Key Words > > However this returns > > ERROR: password is required > DETAIL: Non-superusers must provide a password in the connection string. > > This is my first time trying dblink so there’s probably something simple I’m missing, but I thought I had provided a password.Any ideas? > > Regards > Oliver Kohll > -- Adrian Klaver adrian.klaver@aklaver.com
>> >>> select * from dblink(‘dbname=database2 username=db_link password=mypassword','select username, email from appuser') ast1(username text, email text);: > > I think the problem is the above- ^^^^^^^^^^^^^^^ > > username=db_link should be user=db_link > > The accepted keywords are here: > http://www.postgresql.org/docs/9.3/static/libpq-connect.html#LIBPQ-CONNSTRING Yes that’s it, thanks.