Registering an postgres database through management console via odbc drivers

[vfarmak]

Hi everyone,

We are running SAS 9.4 in a windows server and we want to do the following: install an odbc driver for postgres.

So far these are the steps we have performed:

1. Via the odbc data source administrator (64bit) created a dsn connection (see pictures 1,2). The test is successful
2. I then created in Management Console a File Server and a user for credentials that points to the specific data src (see picture 3)
3. Then I created the corresponding Library by using the ODBC engine (see picture 4)
4. The user that I created in SAS is postgres under a new authentication domain which stores there the credentials for the postgres serve. The user does not belong to a group but the authorization tab has the following roles (PUBLIC, SAS Administrators, SAS System Services and SAS USERS)

Now the problem can be found when I want to register the table in the library. SAS finds the table but when I try to register it gives the following error:

1                                                          The SAS System                           10:38 Tuesday, February 10, 2026

NOTE: Copyright (c) 2023 by SAS Institute Inc., Cary, NC, USA. 
NOTE: SAS (r) Proprietary Software 9.4 (TS1M8) 
      Licensed to GENERALI HELLAS - IAA- PRODUCTION, Site 51800363.
NOTE: This session is executing on the X64_SRV16  platform.



NOTE: Analytical products:
      
      SAS/STAT 15.3

NOTE: Additional host information:

 X64_SRV16 WIN 10.0.14393  Server

NOTE: SAS Initialization used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds
      
NOTE: The autoexec file, D:\SAS\EBIEDIEG\Lev1\SASApp\WorkspaceServer\autoexec.sas, was executed at server initialization.
1          LIBNAME ACTUALS ODBC  DATAsrc=actuals  SCHEMA=public  USER=postgres
1        ! PASSWORD=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX ;
NOTE: Libref ACTUALS was successfully assigned as follows: 
      Engine:        ODBC 
      Physical Name: actuals
2          
2                                                          The SAS System                           10:38 Tuesday, February 10, 2026

3          options VALIDVARNAME=ANY VALIDMEMNAME=EXTEND;
3                                                          The SAS System                           10:38 Tuesday, February 10, 2026

4          proc metalib
5          ;
5        !     omr (LIBURI="A5779UC5.AZ00010W"
6             user="sasadm@saspw" password=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
7             );
8             REPORT(type = summary out = "tab_info");
9             SELECT ("ACTUALS"
10          );
11            FOLDERID = "A5779UC5.AJ000PHV";
12         run;

ERROR: CLI error trying to establish connection: connection to server at "localhost" (::1), port 5432 failed: FATAL:  password 
       authentication failed for user "postgres" 
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE METALIB used (Total process time):
      real time           0.39 seconds
      cpu time            0.07 seconds

The username and the password is correct. However the table cannot be registered.

Please not that the first time it asked for credentials I gave the database credentials of the postgres server.

Can you assist me on that?

Picture 1 : ODBC for Postgres

Picture 2: ODBC for Postgres

Picture 3: Data src that points to the system dsn

Picture 4: SAS Library

[carl_sommer]

The log from table registration doesn't tell the whole back-and-forth dialog that is going on between the SAS Management Console (SMC) GUI client and the SAS workspace server, because to look at the log, it all seems like it happens at once.  Instead, there's a dialog going on between the wizard and the workspace.

When you select the library, SMC requests a workspace server.  If you've ever attempted to register a table and get prompted for SASApp credentials, that is for starting the workspace server.

Once the workspace server is started, SMC checks to see if it needs to prompt for database credentials, based upon how the server connection for the library is defined, and what login credentials are available to the user.   Since you didn't provide the registering user (in your case, sasadm@saspw) a login credential for this authentication domain, you are prompted for the userid and password for postgres.

With the credentials in hand, SMC builds a native libname statement based upon the metadata definition, and submits it to the workspace server, and queries it (via Java) to present the list of tables for the user to select from.  That's when you selected  the Actuals table.

After that, the libref assigned to the native libname is no longer used.

Table registration is then performed using  PROC METALIB code that is built by SMC and submitted to the workspace. The OMR statement uses the  metadata libname engine to assign the library.

This is where things are falling down; using strictly what's in metadata, the metadata identity that is attempting the registration does not have any login information for postgres.

I'd recommend creating a login object on the registering user's account tab with the credentials, or if you are sharing this credential, defining the credential to a group in metadata, and ensuring that the registering user is a member of this group.

Just because you are using sasadm@saspw does not mean that it can read the actual password of another user.   It can't, and that's for solid good security reasons.

Carl Sommer - SAS Technical Support