Re: authentication question

CentOS 4.4 which means postgresql-server-7.4.13-2.RHEL4.1

I'm starting to deal with the notion of allowing other users access
(read only) to a db.

Experimenting on my own db...

host all main_user 192.168.2.10 255.255.255.0 trust
host all all 127.0.0.1 255.255.255.255 trust
host all craig 192.168.2.10 255.255.255.255 pam

because I want to use LDAP authentication via pam.

logs say...
Nov 8 20:18:26 srv1 postgresql: Starting postgresql service: succeeded
Nov 8 20:18:39 srv1 postgres[21020]: PAM audit_open() failed:
Permission denied
Nov 8 20:18:39 srv1 postgres[21020]: [2-1] LOG: pam_authenticate
failed: System error
Nov 8 20:18:39 srv1 postgres[21020]: [3-1] FATAL: PAM authentication
failed for user "craig"

Below is pam info - if anyone can tell me how I might configure this so
I can authenticate via LDAP I would appreciate it.

Craig

# cat /etc/pam.d/postgresql
#%PAM-1.0
auth required pam_stack.so service=system-auth
auth required pam_nologin.so
account required pam_stack.so service=system-auth
password required pam_stack.so service=system-auth
session required pam_stack.so service=system-auth
session required pam_loginuid.so

which was cribbed from /etc/pam.d/sshd

# cat /etc/pam.d/system-auth
#%PAM-1.0
# This file is auto-generated.
# User changes will be destroyed the next time authconfig is run.
auth required /lib/security/$ISA/pam_env.so
auth sufficient /lib/security/$ISA/pam_unix.so likeauth nullok
auth sufficient /lib/security/$ISA/pam_ldap.so use_first_pass
auth required /lib/security/$ISA/pam_deny.so

account required /lib/security/$ISA/pam_unix.so broken_shadow
account sufficient /lib/security/$ISA/pam_succeed_if.so uid < 100
quiet
account [default=bad success=ok
user_unknown=ignore] /lib/security/$ISA/pam_ldap.so
account required /lib/security/$ISA/pam_permit.so

password requisite /lib/security/$ISA/pam_cracklib.so retry=3
password sufficient /lib/security/$ISA/pam_unix.so nullok
use_authtok md5 shadow
password sufficient /lib/security/$ISA/pam_ldap.so use_authtok
password required /lib/security/$ISA/pam_deny.so

session required /lib/security/$ISA/pam_limits.so
session required /lib/security/$ISA/pam_unix.so
session optional /lib/security/$ISA/pam_ldap.so

Craig White wrote:

> logs say...
> Nov 8 20:18:26 srv1 postgresql: Starting postgresql service: succeeded
> Nov 8 20:18:39 srv1 postgres[21020]: PAM audit_open() failed:
> Permission denied
> Nov 8 20:18:39 srv1 postgres[21020]: [2-1] LOG: pam_authenticate
> failed: System error
> Nov 8 20:18:39 srv1 postgres[21020]: [3-1] FATAL: PAM authentication
> failed for user "craig"

I'm not at all familiar with PAM error message wording, but are you
aware that you must create the user "craig" inside the database _as
well_ as on whatever PAM layer you use?

The "audit_open(): Permission denied" message sounds like Postgres is
not authorized to consult PAM though.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Thu, 2006-11-09 at 12:34 -0300, Alvaro Herrera wrote:
> Craig White wrote:
>
> > logs say...
> > Nov 8 20:18:26 srv1 postgresql: Starting postgresql service: succeeded
> > Nov 8 20:18:39 srv1 postgres[21020]: PAM audit_open() failed:
> > Permission denied
> > Nov 8 20:18:39 srv1 postgres[21020]: [2-1] LOG: pam_authenticate
> > failed: System error
> > Nov 8 20:18:39 srv1 postgres[21020]: [3-1] FATAL: PAM authentication
> > failed for user "craig"
>
> I'm not at all familiar with PAM error message wording, but are you
> aware that you must create the user "craig" inside the database _as
> well_ as on whatever PAM layer you use?
>
> The "audit_open(): Permission denied" message sounds like Postgres is
> not authorized to consult PAM though.
----
I did create a user 'craig' in postgres but I agree, that isn't the
issue at this point.

I checked the source rpm to make sure that it was compiled with the pam
option and it appears to me that it was.

I haven't had to fool too much with pam for authenticating other
services so I'm a little bit out of my knowledge base but I know that it
was simple to add netatalk into the pam authentication and expected that
postgresql would be similar.

I have to believe that other people are using pam for authentication
because otherwise, you have to have maintain passwords for each user
within postgresql itself - which seems unwise for many sites.

Still struggling with this...

Craig

Craig White <craigwhite(at)azapple(dot)com> writes:
> I haven't had to fool too much with pam for authenticating other
> services so I'm a little bit out of my knowledge base but I know that it
> was simple to add netatalk into the pam authentication and expected that
> postgresql would be similar.

FWIW, we ship this PAM config file in the Red Hat PG RPMs:

#%PAM-1.0
auth include system-auth
account include system-auth

which AFAIR looks about the same as the corresponding files for other
services. It's installed as /etc/pam.d/postgresql.

I concur with the other response that you need to find out where the
"Permission denied" failure is coming from. There is no "audit_open"
in the Postgres sources so it sounds like an internal failure in the PAM
libraries. If nothing else comes to mind, try strace'ing the postmaster
to see what kernel call draws that failure.

regards, tom lane

On Thu, 2006-11-09 at 11:51 -0500, Tom Lane wrote:
> Craig White <craigwhite(at)azapple(dot)com> writes:
> > I haven't had to fool too much with pam for authenticating other
> > services so I'm a little bit out of my knowledge base but I know that it
> > was simple to add netatalk into the pam authentication and expected that
> > postgresql would be similar.
>
> FWIW, we ship this PAM config file in the Red Hat PG RPMs:
>
> #%PAM-1.0
> auth include system-auth
> account include system-auth
>
> which AFAIR looks about the same as the corresponding files for other
> services. It's installed as /etc/pam.d/postgresql.
----
that doesn't work at all... /var/log/messages reports...
Nov 9 10:26:33 srv1 postgres[6034]: PAM unable to
dlopen(/lib/security/system-auth)
Nov 9 10:26:33 srv1 postgres[6034]: PAM
[dlerror: /lib/security/system-auth: cannot open shared object file: No
such file or directory]

as for what is included BY Red Hat in their postgresql-server rpm...
# rpm -ql postgresql-server|grep pam

returns nothing which makes me double check the spec file on the RPM
which has...
# grep pam /usr/src/redhat/SPECS/postgresql.spec
#work around the undefined or defined to 1 build 6x interaction with the
pam stuff
%{!?build6x:%define non6xpamdeps 1}
%{?build6x:%define non6xpamdeps 0}
%{!?pam:%define pam 1}
%if %pam
%if %non6xpamdeps
BuildPrereq: pam-devel
%if %pam
--with-pam \

a search of Red Hat's bugzilla shows all postgresql bugs closed and
nothing reporting a problem with pam ;-(

and since it does attempt to call pam (as I am seeing in logs), I am
certain that pam option is compiled.

I'm virtually certain that I am better off pointing
to /etc/pam.d/system-auth which clearly works for sshd logins
----
>
> I concur with the other response that you need to find out where the
> "Permission denied" failure is coming from. There is no "audit_open"
> in the Postgres sources so it sounds like an internal failure in the PAM
> libraries. If nothing else comes to mind, try strace'ing the postmaster
> to see what kernel call draws that failure.
----
pretty short strace but I can't see anything that jumps at me and says
aha...

# strace -p 3267
Process 3267 attached - interrupt to quit
select(6, [3 5], NULL, NULL, {95, 566000}) = 1 (in [3], left {88,
881000})
rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS RTMIN
RT_1], NULL, 8) = 0
accept(3, {sa_family=AF_INET, sin_port=htons(56844),
sin_addr=inet_addr("192.168.2.10")}, [16]) = 9
getsockname(9, {sa_family=AF_INET, sin_port=htons(5432),
sin_addr=inet_addr("192.168.2.1")}, [16]) = 0
setsockopt(9, SOL_TCP, TCP_NODELAY, [1], 4) = 0
setsockopt(9, SOL_SOCKET, SO_KEEPALIVE, [1], 4) = 0
clone(child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|
SIGCHLD, child_tidptr=0xb7f2e708) = 5921
close(9) = 0
time(NULL) = 1163093004
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
select(6, [3 5], NULL, NULL, {89, 0}) = ? ERESTARTNOHAND (To be
restarted)
--- SIGCHLD (Child exited) @ 0 (0) ---
rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS RTMIN
RT_1], NULL, 8) = 0
waitpid(-1, [{WIFEXITED(s) && WEXITSTATUS(s) == 0}], WNOHANG) = 5921
send(6, "\2\0\0\0\30\0\0\0\0\0\0\0!\27\0\0\0\0\0\0\0\0\0\0", 24, 0) = 24
waitpid(-1, 0xbfecf5fc, WNOHANG) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
sigreturn() = ? (mask now [])
rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS RTMIN
RT_1], NULL, 8) = 0
time(NULL) = 1163093004
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
select(6, [3 5], NULL, NULL, {89, 0} <unfinished ...>
Process 3267 detached

Craig White <craigwhite(at)azapple(dot)com> writes:
> On Thu, 2006-11-09 at 11:51 -0500, Tom Lane wrote:
>> FWIW, we ship this PAM config file in the Red Hat PG RPMs:

> that doesn't work at all... /var/log/messages reports...

Sorry, I should have mentioned that that was for recent Fedora branches.
In RHEL4 I think this would work:

#%PAM-1.0
auth required pam_stack.so service=system-auth
account required pam_stack.so service=system-auth

> pretty short strace but I can't see anything that jumps at me and says
> aha...

You seem to have only strace'd the postmaster itself --- the interesting
events would be in the child process it forked off. Try "strace -f -p ..."

regards, tom lane

Tom Lane wrote:
> Craig White <craigwhite(at)azapple(dot)com> writes:
> > I haven't had to fool too much with pam for authenticating other
> > services so I'm a little bit out of my knowledge base but I know that it
> > was simple to add netatalk into the pam authentication and expected that
> > postgresql would be similar.
>
> FWIW, we ship this PAM config file in the Red Hat PG RPMs:
>
> #%PAM-1.0
> auth include system-auth
> account include system-auth
>
> which AFAIR looks about the same as the corresponding files for other
> services. It's installed as /etc/pam.d/postgresql.

For this to work you need a system-auth file in /etc/pam.d, which would
have lines for auth/account/required etc, and not just "includes".

PAM seems to be another area on which Linux distributors have been
diverging wildly for a long time; for example here on Debian the include
lines look like

auth requisite pam_nologin.so
auth required pam_env.so
@include common-auth
@include common-account
session required pam_limits.so

so I doubt one distro's config files are applicable to any other.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Thu, 2006-11-09 at 16:34 -0300, Alvaro Herrera wrote:
> Tom Lane wrote:
> > Craig White <craigwhite(at)azapple(dot)com> writes:
> > > I haven't had to fool too much with pam for authenticating other
> > > services so I'm a little bit out of my knowledge base but I know that it
> > > was simple to add netatalk into the pam authentication and expected that
> > > postgresql would be similar.
> >
> > FWIW, we ship this PAM config file in the Red Hat PG RPMs:
> >
> > #%PAM-1.0
> > auth include system-auth
> > account include system-auth
> >
> > which AFAIR looks about the same as the corresponding files for other
> > services. It's installed as /etc/pam.d/postgresql.
>
> For this to work you need a system-auth file in /etc/pam.d, which would
> have lines for auth/account/required etc, and not just "includes".
>
> PAM seems to be another area on which Linux distributors have been
> diverging wildly for a long time; for example here on Debian the include
> lines look like
>
> auth requisite pam_nologin.so
> auth required pam_env.so
> @include common-auth
> @include common-account
> session required pam_limits.so
>
> so I doubt one distro's config files are applicable to any other.
----
and I'm on a Red Hat system which obviously Tom is familiar with since
he is the packager for RH / postgres but I don't think that is the issue
but I have adopted his pam file.

Thanks

Craig

Just in case others follow in my footsteps - this may prove to be
helpful.

Summary of problem: CentOS 4.4 - SELinux enabled - authorizing pam based
users

### Created file /etc/pam.d/postgresql (I'm using LDAP) [*]
# cat /etc/pam.d/postgresql
#%PAM-1.0
auth required pam_stack.so service=system-auth
auth required pam_nologin.so
account required pam_stack.so service=system-auth
password required pam_stack.so service=system-auth
session required pam_stack.so service=system-auth
session required pam_loginuid.so

### Set SELinux security contexts for this file....
# chcon -u system_u -r object_r /etc/pam.d/postgresql

### Already had installed rpm selinux-policy-targeted-sources
### You will need this package
###
### Added to file /etc/selinux/src/targeted/policy/domains/local.te
# cat /etc/selinux/targeted/src/policy/domains/local.te
# postgres/pam
allow postgresql_t self:netlink_audit_socket create;
allow postgresql_t self:netlink_audit_socket nlmsg_relay;
allow postgresql_t self:netlink_audit_socket read;
allow postgresql_t self:netlink_audit_socket write;
allow postgresql_t var_lib_t:file read;

### the last line of the changes to local.te were necessary only for
### postgresql user to be able to read /var/lib/pgsql/.ldaprc
###
### now load this new policy into selinux
# cd /etc/selinux/targeted/src/policy
# make reload

Now, I am able to log in as a user from LDAP - with the obvious
provisions that the user is a user in postgres (password not needed
since that is from LDAP), and pg_hba.conf is properly configured.

[*] Tom's suggestion for /etc/pam.d/postgresql file
#%PAM-1.0
auth required pam_stack.so service=system-auth
account required pam_stack.so service=system-auth

Thanks Tom/Alvaro

Craig