Re: [GENERAL] dblink_connect fails

[James Sewell]

No it is not.

Just in case I tried setting it to 'postgres', logged in without -U
(doesn't work without PGUSER set) and tried the operation again.

Same result.

Cheers,


James Sewell,
PostgreSQL Team Lead / Solutions Architect
__


Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099


On Wed, Dec 16, 2015 at 3:26 PM, Joe Conway  wrote:

> On 12/15/2015 06:24 PM, James Sewell wrote:
> > I have a Windows PostgreSQL server where dblink_connect fails to pick up
> > the current user as follows:
>
> > ffm=# SELECT dblink_connect('master', 'dbname=ffm');
> > ERROR:  could not establish connection
> > DETAIL:  FATAL:  role "PRDSWIDEGRID01$" does not exist
>
> > Has anyone seen this before? It seems very odd to me, I have another
> > identical machine (except for being on 9.4.0) which this works on.
>
>
> Do you happen to have a PGUSER variable defined in your environment
> (that is, in the environment as seen by the OS user the postmaster runs
> under)?
>
> See: http://www.postgresql.org/docs/9.4/static/libpq-envars.html
>
> Joe
>
> --
> Crunchy Data - http://crunchydata.com
> PostgreSQL Support for Secure Enterprises
> Consulting, Training, & Open Source Development
>
>

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [GENERAL] dblink_connect fails

[Joe Conway]

On 12/16/2015 04:53 PM, James Sewell wrote:
> No it is not.
> 
> Just in case I tried setting it to 'postgres', logged in without -U
> (doesn't work without PGUSER set) and tried the operation again.

> > DETAIL:  FATAL:  role "PRDSWIDEGRID01$" does not exist

That "PRDSWIDEGRID01$" is coming from somewhere in your environment.
Looks like it is supposed to be a variable of some sort which is
supposed to resolve to an actual postgres user but for some reason doesn't.

What about FDWs?


-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] dblink_connect fails

[Adrian Klaver]

On 12/16/2015 04:53 PM, James Sewell wrote:

No it is not.

Just in case I tried setting it to 'postgres', logged in without -U
(doesn't work without PGUSER set) and tried the operation again.


Do you mean this:

DETAIL:  FATAL:  role "PRDSWIDEGRID01$" does not exist

or that you could not connect for another reason?

If for the original reason, does the role PRDSWIDEGRID01$ actually exist 
somewhere?





Same result.

Cheers,


James Sewell,
PostgreSQL Team Lead / Solutions Architect
__

Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000***W* www.lisasoft.com *F *(+61) 3 8370 8099

On Wed, Dec 16, 2015 at 3:26 PM, Joe Conway > wrote:

On 12/15/2015 06:24 PM, James Sewell wrote:
> I have a Windows PostgreSQL server where dblink_connect fails to pick up
> the current user as follows:

> ffm=# SELECT dblink_connect('master', 'dbname=ffm');
> ERROR:  could not establish connection
> DETAIL:  FATAL:  role "PRDSWIDEGRID01$" does not exist

> Has anyone seen this before? It seems very odd to me, I have another
> identical machine (except for being on 9.4.0) which this works on.


Do you happen to have a PGUSER variable defined in your environment
(that is, in the environment as seen by the OS user the postmaster runs
under)?

See: http://www.postgresql.org/docs/9.4/static/libpq-envars.html

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development




The contents of this email are confidential and may be subject to legal
or professional privilege and copyright. No representation is made that
this email is free of viruses or other defects. If you have received
this communication in error, you may not copy or distribute any part of
it or otherwise disclose its contents to anyone. Please advise the
sender of your incorrect receipt of this correspondence.




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] dblink_connect fails

[Melvin Davidson]

Perhaps the attached will help. It's a sample function that I wrote and
tested a few years ago on PG v8.3
It worked then, so it should be a good model for you.

On Wed, Dec 16, 2015 at 8:00 PM, Adrian Klaver 
wrote:

> On 12/16/2015 04:53 PM, James Sewell wrote:
>
>> No it is not.
>>
>> Just in case I tried setting it to 'postgres', logged in without -U
>> (doesn't work without PGUSER set) and tried the operation again.
>>
>
> Do you mean this:
>
> DETAIL:  FATAL:  role "PRDSWIDEGRID01$" does not exist
>
> or that you could not connect for another reason?
>
> If for the original reason, does the role PRDSWIDEGRID01$ actually exist
> somewhere?
>
>
>
>> Same result.
>>
>> Cheers,
>>
>>
>> James Sewell,
>> PostgreSQL Team Lead / Solutions Architect
>> __
>>
>> Level 2, 50 Queen St, Melbourne VIC 3000
>>
>> *P *(+61) 3 8370 8000***W* www.lisasoft.com *F *(+61) 3 8370 8099
>>
>> On Wed, Dec 16, 2015 at 3:26 PM, Joe Conway > > wrote:
>>
>> On 12/15/2015 06:24 PM, James Sewell wrote:
>> > I have a Windows PostgreSQL server where dblink_connect fails to
>> pick up
>> > the current user as follows:
>>
>> > ffm=# SELECT dblink_connect('master', 'dbname=ffm');
>> > ERROR:  could not establish connection
>> > DETAIL:  FATAL:  role "PRDSWIDEGRID01$" does not exist
>>
>> > Has anyone seen this before? It seems very odd to me, I have another
>> > identical machine (except for being on 9.4.0) which this works on.
>>
>>
>> Do you happen to have a PGUSER variable defined in your environment
>> (that is, in the environment as seen by the OS user the postmaster
>> runs
>> under)?
>>
>> See: http://www.postgresql.org/docs/9.4/static/libpq-envars.html
>>
>> Joe
>>
>> --
>> Crunchy Data - http://crunchydata.com
>> PostgreSQL Support for Secure Enterprises
>> Consulting, Training, & Open Source Development
>>
>>
>>
>> 
>> The contents of this email are confidential and may be subject to legal
>> or professional privilege and copyright. No representation is made that
>> this email is free of viruses or other defects. If you have received
>> this communication in error, you may not copy or distribute any part of
>> it or otherwise disclose its contents to anyone. Please advise the
>> sender of your incorrect receipt of this correspondence.
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.
-- Function: test_archive(text, text, text, text, date)

-- DROP FUNCTION test_archive(text, text, text, text, date);

CREATE OR REPLACE FUNCTION test_archive(text, text, text, text, date)
  RETURNS bigint AS
$BODY$
-- CREATED BY: Melvin Davidson
-- DATE: 2008-08-11
-- 
-- Execute this function from archive db
-- General flow
-- 1. Open connection to production db 
-- 2. Insert into archive db as select from with criteria
-- 3. Delete from production db with same criteria
-- 4. Close connection.
-- 5. Return the number of TXMaster (Total) records archived.
--
-- ERROR HANDLING
-- If dblink connection fails, -1 is returned
-- If archive date is >= Current Date, -10 is returned
-- If Total number of transactions inserted does not = TXMaster inserts, 
-- rollback is done and -100 returned
-- If Total number of remote transactions deleted does not = remote TXMaster 
deletes, 
-- rollback is done and -200 returned

DECLARE
-- Parameter(s)
p_host_ip   ALIAS FOR $1;
p_dbnameALIAS FOR $2;
p_user  ALIAS FOR $3;
p_passwdALIAS FOR $4;
p_date  ALIAS FOR $5;

-- Variable(s)
v_ctr   BIGINT DEFAULT 0;
v_txmaster_ctr  BIGINT DEFAULT 0;
v_txassetcycle_ctr  BIGINT DEFAULT 0;
v_txassetdamaged_ctrBIGINT DEFAULT 0;
v_txassetfilledemptied_ctr  BIGINT DEFAULT 0;
v_txassetfound_ctr  BIGINT DEFAULT 0;
v_txassetlost_ctr   BIGINT DEFAULT 0;
v_txassetmoved_ctr  BIGINT DEFAULT 0;
v_txassetOwnerChanged_ctr   BIGINT DEFAULT 0;
v_txassetprodasscheck_ctr   BIGINT DEFAULT 0;
v_txassetrepaired_ctr   BIGINT DEFAULT 0;
v_txassettagassigned_ctrBIGINT DEFAULT 0;
v_txbillingaction_ctr   BIGINT DEFAULT 0;
v_txexception_ctr   BIGINT DEFAULT 0;
v_txorderdetailfilled_ctr   BIGINT DEFAULT 0;

Re: [GENERAL] dblink_connect fails

[Joe Conway]

On 12/15/2015 06:24 PM, James Sewell wrote:
> I have a Windows PostgreSQL server where dblink_connect fails to pick up
> the current user as follows:

> ffm=# SELECT dblink_connect('master', 'dbname=ffm');
> ERROR:  could not establish connection
> DETAIL:  FATAL:  role "PRDSWIDEGRID01$" does not exist

> Has anyone seen this before? It seems very odd to me, I have another
> identical machine (except for being on 9.4.0) which this works on.


Do you happen to have a PGUSER variable defined in your environment
(that is, in the environment as seen by the OS user the postmaster runs
under)?

See: http://www.postgresql.org/docs/9.4/static/libpq-envars.html

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature