0

I had a Postgresql DB on a Virtual Machine and I have used below line of code is several functions.

PERFORM dblink_connect('dbname=db_name user=postgres')

Now I am migrating to Azure Postgresql, After several days of process, I have moved the entire application setup to Azure. However I have noticed that all the functions which have above line of code are failing with error below.

SQL Error [2F003]: ERROR: password is required
Detail: Non-superusers must provide a password in the connection string.
Where: SQL statement "SELECT public.dblink_connect(l_current_connection,'dbname=' || CURRENT_DATABASE() || ' port=5432  user=postgres')"

I have changed the code and tried below options too, but with no success.

Option 1

Added password to the code

PERFORM dblink_connect('dbname=db_name user=postgres password=*****')

Error 1

SQL Error [2F003]: ERROR: password is required
Detail: Non-superuser cannot connect if the server does not request a password.
Hint: Target server's authentication method must be changed.
Where: SQL statement "SELECT public.dblink_connect(l_current_connection,'dbname=' || CURRENT_DATABASE() || ' port=5432  user=postgres password=*****')"

Option 2

Notice that I added _u to the function dblink_connect. (This is per some online articles)

PERFORM dblink_connect_u('dbname=db_name user=postgres password=*****')

Error 2

SQL Error [2F003]: ERROR: password is required
Detail: Non-superuser cannot connect if the server does not request a password.
Hint: Target server's authentication method must be changed.
Where: SQL statement "SELECT public.dblink_connect_u(l_current_connection,'dbname=' || CURRENT_DATABASE() || ' port=5432  user=postgres password=*****')"

Per the Errors, the user (postgres) need to be a superuser for the code to work, however what I have read on Azure documentation is that a superuser can not be created on an Azure postgresql.

Is it really not possible to create a Superuser on Azure Postgres DB?

If not, then How can I make the below code work, Any options would be greatly helpful.

If there is no solution to this, then unfortunately I need to roll back the PostgresDB to VM again.

VIRIYALA NARESH
  • 187
  • 1
  • 4
  • 17

3 Answers3

0

Look at the

Hint: Target server's authentication method must be changed.

You have to configure the database server that you are connecting to so that the connection requires a password. dblink will refuse to connect without being asked for credentials.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Could you please point me to any document to make this change. Note: This is an Azure PostgresSQL – VIRIYALA NARESH Feb 15 '22 at 15:20
  • No idea how to adjust `pg_hba.conf` on a Microsoft-hosted database. – Laurenz Albe Feb 15 '22 at 15:21
  • Still waiting for some help on this topic, I did a lot of research on the internet on this topic, couldn't find anything to show how to make changes to pg_hba.conf file on Azure Postgres as suggested by Laurenz. – VIRIYALA NARESH Feb 18 '22 at 02:48
0

This is the PostgreSQL configuration issue.

After changing from trust to md5 problem is solved.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

I'm able to use pre defined link the link in queries now.

SELECT data.version FROM fn.dblink('testlink', 'SELECT VERSION()'::text) data("version" text);
D.A.H
  • 858
  • 2
  • 9
  • 19
-1

We encountered the same problem . The way we resolved this was just by adding the parameter hostaddr=127.0.0.1 to the connection string.

i.e. dblink('hostaddr=127.0.0.1 dbname=db_name user=postgres password=***** port=5432 ')

chr alex
  • 1
  • 3
  • This does not solve the problem. If you want to execute like `SELECT data.version FROM fn.dblink('fs_bdm', 'SELECT VERSION()'::text) data("version" text);`then it fails for non superuser. This way you can define query inside of view or function without revealing or hard coding any user credentials. – D.A.H Jul 21 '23 at 17:53