topic SAS libname postgres set role option in Administration and Deployment

SAS libname postgres set role option

sebster24 — Wed, 09 Oct 2019 12:43:40 GMT

Hello,

Does anyone know how to set the role in the libname statement for connecting for postgres?

I am currently in the process of saving some datasets from SAS to postgres. I am unable to set a "Role" to be able to write tables into Postgres. The role assigns multiple users the privilge of being able to read/write data with restricted admin privileges limited to data manipulation.

I tried creating these datasets using the following code block results in the datasets saved in Postgres only being viewable to Tom (user=Tom). This works, but no other user other than "Tom" can read/write/Amend the data

libname psql postgres server=localhost port=5432 user= Tom password= "mypassword" 
database=test schema= "dbtest"; /*change user*/


data psql.tmp ;
set sashelp.shoes;
run;

In order to bypass this read/write issue, i set the role="AdminUse" as noted in the following approach. This works, but means i need to include this code block at every step.


proc sql noerrorstop;
   connect to psql as x1(server=localhost port=5432 
      user=Tom password="mypassword" database=test); /*change username here*/

execute( SET ROLE AdminUse;
         CREATE TABLE psql.tmp
         (no int primary key, state varchar(10));
		 RESET ROLE;
          ) by x1;
disconnect from x1;
quit;

Does anyone know how to set the role in the libname statement for connecting for postgres?

Re: SAS libname postgres set role option

AnandVyas — Mon, 14 Oct 2019 02:34:13 GMT

Hi @sebster24

You can use the option 'conopts' to specify few of these options in a libname statement. You can find example for this in the documentation link.

Example:	This example specifies an SSL mode of required for PostgreSQL.
libname pg postgres user=myusr1 pwd=mypwd1 server='mysrv.com' 
     port=5432 db=userpg conopts='sslmode=required';

Tip:	You can also specify any of these additional connection options: DATABASE=, ROLE=, SCHEMA=, and WAREHOUSE=.

Re: SAS libname postgres set role option

ronan — Tue, 22 Oct 2019 13:14:15 GMT

DBCONINIT libname option might be useful also.

https://documentation.sas.com/?docsetId=acreldb&docsetTarget=p1agt4p0hj4fyun103no9xpgskdv.htm&docsetVersion=9.4&locale=en