Обсуждение: Postgresql gss user map doesn't work

Поиск
Список
Период
Сортировка

Postgresql gss user map doesn't work

От
xujian
Дата:
Hello,
        I have a problem when I am using gss map. I want to map the user xxx@company.com to db role company_com_xxx
here is my pa_hba.conf:
=================
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host      all                      all                all                             gss include_realm=1 map=mymap

here is the pg_ident.conf
=================
# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
mymap        /(.*)@COMPANY.COM              company_com_\1

However, it doesn't work, I got error message
=================
LOG:  no match in usermap "mymap" for user "xxx" authenticated as "xxx@COMPANY.COM"
FATAL:  GSSAPI authentication failed for user "xxx"
DETAIL:  Connection matched pg_hba.conf line 88: "host    all             all       all                  gss include_realm=1 map=mymap"


but if I changed the map to 
=================
# MAPNAME       SYSTEM-USERNAME               PG-USERNAME
mymap               /(.*)@COMPANY.COM              \1

then I can login, I have created role xxx and company_com_xxx in db side. even if I hard code the username in the mapping like
=================
# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
mymap        /(.*)@COMPANY.COM              company_com_xxx

it still doesn't work. any idea? 

Thanks in advance!

James

Re: Postgresql gss user map doesn't work

От
xujian
Дата:
it looks like I need to specify the mapping user name in the command, for instance, if my credential is xxx, I want to login as user company_com_xxx, I have to run command like

/psql -d dbname -h postgresql.server.name -U company_com_xxx

but why I need to specify the mapping user name company_com_xxx in command line? 
does anyone have same issue? thanks

James


From: jamesxu@outlook.com
To: pgsql-admin@postgresql.org
Subject: Postgresql gss user map doesn't work
Date: Tue, 30 Jun 2015 12:56:47 -0400

Hello,
        I have a problem when I am using gss map. I want to map the user xxx@company.com to db role company_com_xxx
here is my pa_hba.conf:
=================
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host      all                      all                all                             gss include_realm=1 map=mymap

here is the pg_ident.conf
=================
# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
mymap        /(.*)@COMPANY.COM              company_com_\1

However, it doesn't work, I got error message
=================
LOG:  no match in usermap "mymap" for user "xxx" authenticated as "xxx@COMPANY.COM"
FATAL:  GSSAPI authentication failed for user "xxx"
DETAIL:  Connection matched pg_hba.conf line 88: "host    all             all       all                  gss include_realm=1 map=mymap"


but if I changed the map to 
=================
# MAPNAME       SYSTEM-USERNAME               PG-USERNAME
mymap               /(.*)@COMPANY.COM              \1

then I can login, I have created role xxx and company_com_xxx in db side. even if I hard code the username in the mapping like
=================
# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
mymap        /(.*)@COMPANY.COM              company_com_xxx

it still doesn't work. any idea? 

Thanks in advance!

James

Re: Postgresql gss user map doesn't work

От
Scott Whitney
Дата:
Well, that's just going to be your underlying authentication method. Say you want to authenticate via LDAP using ADS. Well, you have 2 basic choice. "My name@mydomain.whatever.internal.local" Probably .local. You're just asking PG to auth against "something else." You have to configure that in the underlying OS/directory store.

-------- Original message --------
From: xujian <jamesxu@outlook.com>
Date: 06/30/2015 4:40 PM (GMT-06:00)
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Postgresql gss user map doesn't work

it looks like I need to specify the mapping user name in the command, for instance, if my credential is xxx, I want to login as user company_com_xxx, I have to run command like

/psql -d dbname -h postgresql.server.name -U company_com_xxx

but why I need to specify the mapping user name company_com_xxx in command line? 
does anyone have same issue? thanks

James


From: jamesxu@outlook.com
To: pgsql-admin@postgresql.org
Subject: Postgresql gss user map doesn't work
Date: Tue, 30 Jun 2015 12:56:47 -0400

Hello,
        I have a problem when I am using gss map. I want to map the user xxx@company.com to db role company_com_xxx
here is my pa_hba.conf:
=================
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host      all                      all                all                             gss include_realm=1 map=mymap

here is the pg_ident.conf
=================
# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
mymap        /(.*)@COMPANY.COM              company_com_\1

However, it doesn't work, I got error message
=================
LOG:  no match in usermap "mymap" for user "xxx" authenticated as "xxx@COMPANY.COM"
FATAL:  GSSAPI authentication failed for user "xxx"
DETAIL:  Connection matched pg_hba.conf line 88: "host    all             all       all                  gss include_realm=1 map=mymap"


but if I changed the map to 
=================
# MAPNAME       SYSTEM-USERNAME               PG-USERNAME
mymap               /(.*)@COMPANY.COM              \1

then I can login, I have created role xxx and company_com_xxx in db side. even if I hard code the username in the mapping like
=================
# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
mymap        /(.*)@COMPANY.COM              company_com_xxx

it still doesn't work. any idea? 

Thanks in advance!

James



Journyx, Inc.
7600 Burnet Road #300
Austin, TX 78757
www.journyx.com

p 512.834.8888 
f 512-834-8858 

Do you receive our promotional emails? You can subscribe or unsubscribe to those emails at http://go.journyx.com/emailPreference/e/4932/714/ 

Re: Postgresql gss user map doesn't work

От
xujian
Дата:
Thanks Scott. I think this is what pg_ident.conf does, right?
in pg_ident.conf , I specify the mapping rule to map xxx@COMPANY.COM username to pg user name company_com_xxx

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
mymap               /^(.*)@COMPANY\.COM$    company_com_\1

in postgresql document, it has the example which is similar with mine, but it is not gss authentication
mymap   /^(.*)@mydomain\.com$      \1
mymap   /^(.*)@otherdomain\.com$   guest
I am not sure why I am not able to map my credential to other db users. thanks

James




From: scott@journyx.com
To: jamesxu@outlook.com; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Postgresql gss user map doesn't work
Date: Tue, 30 Jun 2015 21:56:54 +0000

Well, that's just going to be your underlying authentication method. Say you want to authenticate via LDAP using ADS. Well, you have 2 basic choice. "My name@mydomain.whatever.internal.local" Probably .local. You're just asking PG to auth against "something else." You have to configure that in the underlying OS/directory store.

-------- Original message --------
From: xujian <jamesxu@outlook.com>
Date: 06/30/2015 4:40 PM (GMT-06:00)
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Postgresql gss user map doesn't work

it looks like I need to specify the mapping user name in the command, for instance, if my credential is xxx, I want to login as user company_com_xxx, I have to run command like

/psql -d dbname -h postgresql.server.name -U company_com_xxx

but why I need to specify the mapping user name company_com_xxx in command line? 
does anyone have same issue? thanks

James


From: jamesxu@outlook.com
To: pgsql-admin@postgresql.org
Subject: Postgresql gss user map doesn't work
Date: Tue, 30 Jun 2015 12:56:47 -0400

Hello,
        I have a problem when I am using gss map. I want to map the user xxx@company.com to db role company_com_xxx
here is my pa_hba.conf:
=================
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host      all                      all                all                             gss include_realm=1 map=mymap

here is the pg_ident.conf
=================
# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
mymap        /(.*)@COMPANY.COM              company_com_\1

However, it doesn't work, I got error message
=================
LOG:  no match in usermap "mymap" for user "xxx" authenticated as "xxx@COMPANY.COM"
FATAL:  GSSAPI authentication failed for user "xxx"
DETAIL:  Connection matched pg_hba.conf line 88: "host    all             all       all                  gss include_realm=1 map=mymap"


but if I changed the map to 
=================
# MAPNAME       SYSTEM-USERNAME               PG-USERNAME
mymap               /(.*)@COMPANY.COM              \1

then I can login, I have created role xxx and company_com_xxx in db side. even if I hard code the username in the mapping like
=================
# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
mymap        /(.*)@COMPANY.COM              company_com_xxx

it still doesn't work. any idea? 

Thanks in advance!

James



Journyx, Inc.
7600 Burnet Road #300
Austin, TX 78757
www.journyx.com

p 512.834.8888 
f 512-834-8858 

Do you receive our promotional emails? You can subscribe or unsubscribe to those emails at http://go.journyx.com/emailPreference/e/4932/714/ 

Re: Postgresql gss user map doesn't work

От
Magnus Hagander
Дата:


On Tue, Jun 30, 2015 at 11:37 PM, xujian <jamesxu@outlook.com> wrote:
it looks like I need to specify the mapping user name in the command, for instance, if my credential is xxx, I want to login as user company_com_xxx, I have to run command like

/psql -d dbname -h postgresql.server.name -U company_com_xxx

but why I need to specify the mapping user name company_com_xxx in command line? 
does anyone have same issue? thanks



Yes, that is working as intended. You always have to tell postgres which user you want to log in with, pg_ident only allows you to authenticate with a different name, you still have to tell the system which one you want.

You can also put the username in the PGUSER environment variable if it's something you want to deploy across many users.


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: Postgresql gss user map doesn't work

От
xujian
Дата:
Thanks Magnus, you are awesome!

James


Date: Wed, 1 Jul 2015 08:38:45 +0200
Subject: Re: [ADMIN] Postgresql gss user map doesn't work
From: magnus@hagander.net
To: jamesxu@outlook.com
CC: pgsql-admin@postgresql.org



On Tue, Jun 30, 2015 at 11:37 PM, xujian <jamesxu@outlook.com> wrote:
it looks like I need to specify the mapping user name in the command, for instance, if my credential is xxx, I want to login as user company_com_xxx, I have to run command like

/psql -d dbname -h postgresql.server.name -U company_com_xxx

but why I need to specify the mapping user name company_com_xxx in command line? 
does anyone have same issue? thanks



Yes, that is working as intended. You always have to tell postgres which user you want to log in with, pg_ident only allows you to authenticate with a different name, you still have to tell the system which one you want.

You can also put the username in the PGUSER environment variable if it's something you want to deploy across many users.


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: Postgresql gss user map doesn't work

От
xujian
Дата:
Since we need to add user name in the command, which is not we want, I removed the map in the pg_ident.conf file, and created role xxx@COMPANY.COM in postgresql

the pg_hba.conf looks like:
host    all     all    all    gss include_realm=1

in pg_ident.conf, I removed all items

in postgresql, I create role
create role "xxx@COMPANY.COM" login

I thought it would work, because my credential is xxx@COMPANY.COM, and there was user xxx@COMPANY.COM in postgresql, it should map the my credential to user xxx@COMPANY.COM.

however, when I login with kerberos, I got error  below on server side
LOG:  provided user name (xxx) and authenticated user name (xxx@COMPANY.COM) do not match
FATAL:  GSSAPI authentication failed for user "xxx"

Do anyone know why it doesn't work?  thanks

James


From: jamesxu@outlook.com
To: magnus@hagander.net
CC: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Postgresql gss user map doesn't work
Date: Wed, 1 Jul 2015 09:09:28 -0400

Thanks Magnus, you are awesome!

James


Date: Wed, 1 Jul 2015 08:38:45 +0200
Subject: Re: [ADMIN] Postgresql gss user map doesn't work
From: magnus@hagander.net
To: jamesxu@outlook.com
CC: pgsql-admin@postgresql.org



On Tue, Jun 30, 2015 at 11:37 PM, xujian <jamesxu@outlook.com> wrote:
it looks like I need to specify the mapping user name in the command, for instance, if my credential is xxx, I want to login as user company_com_xxx, I have to run command like

/psql -d dbname -h postgresql.server.name -U company_com_xxx

but why I need to specify the mapping user name company_com_xxx in command line? 
does anyone have same issue? thanks



Yes, that is working as intended. You always have to tell postgres which user you want to log in with, pg_ident only allows you to authenticate with a different name, you still have to tell the system which one you want.

You can also put the username in the PGUSER environment variable if it's something you want to deploy across many users.


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Tuning on server with both running oracle and postgreSQL database

От
koff10
Дата:
Hi all,
Is there anyone experienced in    memory management about server running
oracle and postgres database ?
I have server running on Redhat 6.4 with  64 Go of physical memory.

I want to install Postgresql onto a "production" server
that currently runs Oracle. Are there any problems with running Postgresql
and Oracle on the same machine.

Anything special to know about memory,  SHMMAX,SHMALL ,disk layout, and
things like that?
thanks

koff

Re: Tuning on server with both running oracle and postgreSQL database

От
Scott Marlowe
Дата:
On Wed, Jul 1, 2015 at 3:00 PM, koff10 <koff10@hotmail.com> wrote:
>
> Hi all,
> Is there anyone experienced in    memory management about server running
> oracle and postgres database ?
> I have server running on Redhat 6.4 with  64 Go of physical memory.
>
> I want to install Postgresql onto a "production" server
> that currently runs Oracle. Are there any problems with running Postgresql
> and Oracle on the same machine.
>
> Anything special to know about memory,  SHMMAX,SHMALL ,disk layout, and
> things like that?
> thanks

How hard are each db engine going to be working? If postgresql just
needs to store a few thousand rows then no tuning is needed. If both
work as hard as each other, then you'll need to "steal" some memory
from Oracle to let postgresql run well.

Will Oracle be doing most of the work?

Are you planning a migration from Oracle to PostgreSQL and this is a
temporary thing?

Prior to 9.4 you'll have to worry about shmmax/shmmall and making sure
they're big enough for the shared_buffers you want pgsql to have. 1G
is usually plenty for most uses.

Keep postgresql's work_mem smallish esp if you're gonna have a lot of
connections to it. Use connection pooling if you're gonna have more
than a few dozen connections to postgresql.

But the big question is, what's your usage planned to look like? That
guides a LOT of decisions here.

Re: Postgresql gss user map doesn't work

От
Magnus Hagander
Дата:


On Wed, Jul 1, 2015 at 7:36 PM, xujian <jamesxu@outlook.com> wrote:
Since we need to add user name in the command, which is not we want, I removed the map in the pg_ident.conf file, and created role xxx@COMPANY.COM in postgresql

the pg_hba.conf looks like:
host    all     all    all    gss include_realm=1

in pg_ident.conf, I removed all items

in postgresql, I create role
create role "xxx@COMPANY.COM" login

I thought it would work, because my credential is xxx@COMPANY.COM, and there was user xxx@COMPANY.COM in postgresql, it should map the my credential to user xxx@COMPANY.COM.

however, when I login with kerberos, I got error  below on server side
LOG:  provided user name (xxx) and authenticated user name (xxx@COMPANY.COM) do not match
FATAL:  GSSAPI authentication failed for user "xxx"

Do anyone know why it doesn't work?  thanks



The PostgreSQL clients (psql in this case) will default to what you are logged into on the local machine with (the result of getpwuid()). This returns "xxx", so that's what PostgreSQL logs in with. It does not explicitly ask the gss system what credentials are there until a much later stage.

To do what you want, you need to create "xxx" in the database, and have a pg_ident mapping xxx@COMPANY.COM to xxx (using a regexp map probably).
 


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/