Home > mailing lists

Thread: Postgresql gss user map doesn't work

Postgresql gss user map doesn't work

From

xujian

Date:

30 June 2015, 19:56:54

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

From

xujian

Date:

01 July 2015, 00:38:03

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

From

Scott Whitney

Date:

01 July 2015, 00:57:12

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

From

xujian

Date:

01 July 2015, 01:21:15

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

http://www.postgresql.org/docs/9.1/static/auth-username-maps.html

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

From

Magnus Hagander

Date:

01 July 2015, 09:38:51

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

From

xujian

Date:

01 July 2015, 16:09:39

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

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

From

xujian

Date:

01 July 2015, 20:36:31

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

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

From

koff10

Date:

02 July 2015, 00:00:51

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

From

Scott Marlowe

Date:

02 July 2015, 00:57:23

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

From

Magnus Hagander

Date:

02 July 2015, 13:19:55

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/