Postgres Users, Windows Server Users and groups

[Paul Mulroney]

Hi Martin,

This is what the code would look like in Omnis - you don't need to create an Omnis Scheme to fetch the information, just load the results into a list: (Studio 10.2)

Create a list var vlResults, and two char vars role_name and role_attributes.  Have an object var voStatement, and a session object (voSession) already defined and setup.

# Assume that you already have a Postgres session that you've connected to.
Do vlResults.$define(role_name,role_attributes) ## Define the list to hold the results
Do voSession.$newstatement Returns voStatement ## Get a new statement object to process
Begin statement ## Setup the SQL query in Postgres.
Sta: SELECT usename AS role_name,
Sta: CASE
Sta:   WHEN usesuper AND usecreatedb THEN
Sta:     CAST('superuser, create database' AS pg_catalog.text)
Sta:   WHEN usesuper THEN
Sta:     CAST('superuser' AS pg_catalog.text)
Sta:   WHEN usecreatedb THEN
Sta:     CAST('create database' AS pg_catalog.text)
Sta:   ELSE
Sta:     CAST('' AS pg_catalog.text)
Sta: END role_attributes
Sta: FROM pg_catalog.pg_user
Sta: ORDER BY role_name desc;
End statement
Get statement vsQuery ## Get in a char field so we can debug later if we need to.
Do voStatement.$execdirect(vsQuery) ## Run it
If voStatement.$errorcode=kDAMNoError ## Was there an error?
Do voStatement.$fetch(vlResults,kFetchAll) ## No, get all results.
Else
# Report error here
End If
# At this point you'll have a list of users and roles in vlResults
Quit method 


Regards,
Paul.


> On 29 Aug 2022, at 6:12 pm, Martin Obongita <martin.obongita at yahoo.com> wrote:
> 
> Hi Paul,
> 
> Thank you for the query.
> 
> How do I create the Omnis schema for the Postgres users?
> 
> Rgds,
> Martin O.
> 
> On Monday, August 29, 2022 at 04:27:41 AM GMT+3, Paul Mulroney <pmulroney at logicaldevelopments.com.au> wrote:
> 
> 
> Hi Martin,
> 
> You can get the list of Postgres users with a query:
> 
> SELECT usename AS role_name,
> CASE
>   WHEN usesuper AND usecreatedb THEN
>     CAST('superuser, create database' AS pg_catalog.text)
>   WHEN usesuper THEN
>     CAST('superuser' AS pg_catalog.text)
>   WHEN usecreatedb THEN
>     CAST('create database' AS pg_catalog.text)
>   ELSE
>     CAST('' AS pg_catalog.text)
> END role_attributes
> FROM pg_catalog.pg_user
> ORDER BY role_name desc;
> 
> This is assuming you have already logged into Postgres.
> 
> Regards,
> Paul.
> 
> 
> > On 29 Aug 2022, at 6:34 am, Martin Obongita via omnisdev-en <omnisdev-en at lists.omnis-dev.com> wrote:
> > 
> > Hi ALL,
> > How do I get a list of all users created in my Postgres database on to Omnis?How do I get a list of all active directory users and groups created in a windows server?Is there a function to get this info?
> > I wonder if its also possible to get the passwords as well.
> > In other words, is there a way to setup my application to use the two securities already setup to logon to my application?I wish to have the option for end-users to chose to logon to my application with either setup.
> > 
> > Kind regards,Martin O. 
> > _____________________________________________________________
> > Manage your list subscriptions at https://lists.omnis-dev.com
> > Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 
> 
> 
> If you are wise you will only believe half of what you hear.  if you are brilliant you will know which half to believe.
> -- 
> Paul W. Mulroney                                            We Don't Do Simple Pty Ltd 
> pmulroney at logicaldevelopments.com.au      Trading as Logical Developments
> www.logicaldevelopments.com.au                  ACN 161 009 374 
> Ph: +61 8 9458 3889                                      86 Coolgardie Street
>                                                                         BENTLEY  WA  6102
> 
> 
> 
> 




 	Paul W Mulroney
Logical Developments
Customised Software Solutions
Ph: 08 9458 3889
 	We Don't Do Simple Pty Ltd
trading as
Logical Developments
ACN 161 009 374