Thread: fallback authentication

fallback authentication

From
Ron Peterson
Date:
I've configured PostgreSQL (8.0.0beta5) to do ldap authenticatation via
pam for connections to localhost.  My hba.conf looks like:

host    all         all         127.0.0.1         255.255.255.255   pam

My pam.d/postgresql file looks like:

auth     required     pam_ldap.so
account  required     pam_ldap.so

This all works great.

Sometimes, however, I would like to create an account in PostgreSQL
which I do not want to also maintain in LDAP.  Is it possible to
configure authentication to fall through to a different method?

--
Ron Peterson
Network & Systems Manager
Mount Holyoke College
http://www.mtholyoke.edu/~rpeterso

Re: fallback authentication

From
Ron Peterson
Date:
On Fri, Dec 10, 2004 at 03:20:50PM -0500, Ron Peterson wrote:

> Sometimes, however, I would like to create an account in PostgreSQL
> which I do not want to also maintain in LDAP.  Is it possible to
> configure authentication to fall through to a different method?

I suppose the right thing to do is either

* don't be lazy, and update my LDAP maintainance to include the
  required accounts, or

* fall through in pam.  Is there anything similar in concept to
  libpam-pgsql, but which simply authenticates against PostgreSQL's
  built-in authentication mechanism?

--
Ron Peterson
Network & Systems Manager
Mount Holyoke College
http://www.mtholyoke.edu/~rpeterso

Re: fallback authentication

From
Bruno Wolff III
Date:
On Fri, Dec 10, 2004 at 20:50:56 -0500,
  Ron Peterson <rpeterso@mtholyoke.edu> wrote:
> On Fri, Dec 10, 2004 at 03:20:50PM -0500, Ron Peterson wrote:
>
> > Sometimes, however, I would like to create an account in PostgreSQL
> > which I do not want to also maintain in LDAP.  Is it possible to
> > configure authentication to fall through to a different method?
>
> I suppose the right thing to do is either
>
> * don't be lazy, and update my LDAP maintainance to include the
>   required accounts, or
>
> * fall through in pam.  Is there anything similar in concept to
>   libpam-pgsql, but which simply authenticates against PostgreSQL's
>   built-in authentication mechanism?

You can put per user exceptions first in your pg_hba.conf file. That way
these people will be handled by those rules, but other users can be
authenticated using pam.

Re: fallback authentication

From
Ron Peterson
Date:
On Sat, Dec 11, 2004 at 01:51:07PM -0600, Bruno Wolff III wrote:
> On Fri, Dec 10, 2004 at 20:50:56 -0500,
>   Ron Peterson <rpeterso@mtholyoke.edu> wrote:
> > On Fri, Dec 10, 2004 at 03:20:50PM -0500, Ron Peterson wrote:
> >
> > > Sometimes, however, I would like to create an account in PostgreSQL
> > > which I do not want to also maintain in LDAP.  Is it possible to
> > > configure authentication to fall through to a different method?
> >
> > I suppose the right thing to do is either
> >
> > * don't be lazy, and update my LDAP maintainance to include the
> >   required accounts, or
> >
> > * fall through in pam.  Is there anything similar in concept to
> >   libpam-pgsql, but which simply authenticates against PostgreSQL's
> >   built-in authentication mechanism?
>
> You can put per user exceptions first in your pg_hba.conf file. That way
> these people will be handled by those rules, but other users can be
> authenticated using pam.

I have:

host    all         all         127.0.0.1         255.255.255.255   md5
host    all         all         127.0.0.1         255.255.255.255   pam postgresql
host    all         all         0.0.0.0           0.0.0.0           reject

I've also tried reversing the first two lines.  Either strategy
individually works, but I'd like lookups which don't work locally to try
pam (or vice-versa).  What am I missing?

I have to use pam to authenticate my local userbase, unless I start also
maintaining the necessary postgresql password hash.  But I'd like to
also have a few local administrative accounts that don't exist in ldap.
Bottom line is, I can always put them in ldap if I really have to; I was
just hoping there was a lazier way.  I feel like I'm working harder at
being lazy than if I'd just tweak my ldap account maintainance
procedures, though... ;)

--
Ron Peterson
Network & Systems Manager
Mount Holyoke College
http://www.mtholyoke.edu/~rpeterso

i need help

From
"Philip Michael D Vargas"
Date:
Good day to all ...

I hope any one can give me an advice ... to optimize my database...

im having a problem when i'm backing up my DB and also using vacuum for my
DB...
my CPU load goes up.... and no one can use my DB... most of the transaction
comes from web server..

I just need ur good advice ...

thank you

please check my postgresql.conf
--
#
#
# Connection Parameters
#
tcpip_socket = true
#ssl = false

max_connections = 300
superuser_reserved_connections = 100

port = 5432
#hostname_lookup = false
#show_source_port = false

#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal

#virtual_host = ''

#krb_server_keyfile = ''


#
# Shared Memory Size
#
shared_buffers = 600  # min max_connections*2 or 16, 8KB each
#max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes
#max_fsm_pages = 10000  # min 1000, fsm is free space map, ~6 bytes
#max_locks_per_transaction = 64 # min 10
#wal_buffers = 8  # min 4, typically 8KB each

#
# Non-shared Memory Sizes
#
#sort_mem = 1024  # min 64, size in KB
#vacuum_mem = 8192  # min 1024, size in KB


#
# Write-ahead log (WAL)
#
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#
#commit_delay = 0  # range 0-100000, in microseconds
#commit_siblings = 5  # range 1-1000
#
#fsync = true
#wal_sync_method = fsync # the default varies across platforms:
#    # fsync, fdatasync, open_sync, or open_datasync
#wal_debug = 0   # range 0-16


#
# Optimizer Parameters
#
enable_seqscan = true
enable_indexscan = true
enable_tidscan = true
enable_sort = true
enable_nestloop = true
enable_mergejoin = true
enable_hashjoin = true

effective_cache_size = 1000 # typically 8KB each
random_page_cost = 4  # units are one sequential page fetch cost
cpu_tuple_cost = 0.01  # (same)
cpu_index_tuple_cost = 0.001 # (same)
cpu_operator_cost = 0.0025 # (same)

default_statistics_target = 10 # range 1-1000

#
# GEQO Optimizer Parameters
#
geqo = true
geqo_selection_bias = 2.0 # range 1.5-2.0
geqo_threshold = 11
geqo_pool_size = 1024  # default based on tables in statement,
    # range 128-1024
geqo_effort = 1
geqo_generations = 0
geqo_random_seed = -1  # auto-compute seed


#
# Message display
#
#server_min_messages = notice # Values, in order of decreasing detail:
    #   debug5, debug4, debug3, debug2, debug1,
    #   info, notice, warning, error, log, fatal,
    #   panic
#client_min_messages = notice # Values, in order of decreasing detail:
    #   debug5, debug4, debug3, debug2, debug1,
    #   log, info, notice, warning, error
#silent_mode = false

log_connections = true
#log_pid = false
log_statement = true
log_duration = true
log_timestamp = true

#log_min_error_statement = panic # Values in order of increasing severity:
     #   debug5, debug4, debug3, debug2, debug1,
     #   info, notice, warning, error, panic(off)

#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false

#explain_pretty_print = true

# requires USE_ASSERT_CHECKING
#debug_assertions = true


#
# Syslog
#
syslog = 2   # range 0-2
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'


#
# Statistics
#
show_parser_stats = false
show_planner_stats = false
show_executor_stats = false
show_statement_stats = false

# requires BTREE_BUILD_STATS
#show_btree_build_stats = false


#
# Access statistics collection
#
stats_start_collector = false
stats_reset_on_server_start = false
stats_command_string = false
stats_row_level = false
stats_block_level = false


#
# Lock Tracing
#
#trace_notify = false

# requires LOCK_DEBUG
#trace_locks = false
#trace_userlocks = false
#trace_lwlocks = false
#debug_deadlocks = false
#trace_lock_oidmin = 16384
#trace_lock_table = 0


#
# Misc
#
autocommit = true
#dynamic_library_path = '$libdir'
#search_path = '$user,public'
#datestyle = 'iso, us'
#timezone = unknown  # actually, defaults to TZ environment setting
#australian_timezones = false
#client_encoding = sql_ascii # actually, defaults to database encoding
#authentication_timeout = 60 # 1-600, in seconds
#deadlock_timeout = 1000 # in milliseconds
#default_transaction_isolation = 'read committed'
#max_expr_depth = 10000  # min 10
#max_files_per_process = 1000 # min 25
#password_encryption = true
#sql_inheritance = true
#transform_null_equals = false
#statement_timeout = 0  # 0 is disabled, in milliseconds
#db_user_namespace = false



#
# Locale settings
#
# (initialized by initdb -- may be changed)
LC_MESSAGES = 'en_US.UTF-8'
LC_MONETARY = 'en_US.UTF-8'
LC_NUMERIC = 'en_US.UTF-8'
LC_TIME = 'en_US.UTF-8'

-----------------

here is my diskspace..
/dev/sdb1              3526172   1132784   2214268  34% /
/dev/sda1               248895      8796    227249   4% /boot
none                   2005700         0   2005700   0% /dev/shm
/dev/md0              65757260  50992580  11424376  82% /var
/dev/sdc1             17409840  13521548   3003916  82% /backup
----------------


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)


Re: fallback authentication

From
Bruno Wolff III
Date:
On Sat, Dec 11, 2004 at 22:55:55 -0500,
  Ron Peterson <rpeterso@mtholyoke.edu> wrote:
>
> I have:
>
> host    all         all         127.0.0.1         255.255.255.255   md5
> host    all         all         127.0.0.1         255.255.255.255   pam postgresql
> host    all         all         0.0.0.0           0.0.0.0           reject
>
> I've also tried reversing the first two lines.  Either strategy
> individually works, but I'd like lookups which don't work locally to try
> pam (or vice-versa).  What am I missing?

You can't use 'all' for the username specification. You need to explicitly
list out the usernames in the first host line. (Which should be the md5
line.)

Re: fallback authentication

From
Dmitry Morozovsky
Date:
On Sat, 11 Dec 2004, Ron Peterson wrote:

RP> I have:
RP>
RP> host    all         all         127.0.0.1         255.255.255.255   md5
RP> host    all         all         127.0.0.1         255.255.255.255   pam postgresql
RP> host    all         all         0.0.0.0           0.0.0.0           reject

This scheme would not work. However, something like the following may help:

local   all             pgsql   ident   sameuser

host    all        dba    127.0.0.1    255.255.255.255        md5
host    all        local    127.0.0.1    255.255.255.255        pam  postgresql

So you can do local maintenance like cron backups from pgsql account, and
fallback login for dba user when pam or authenticating modules are not
available.


Sincerely,
D.Marck                                     [DM5020, MCK-RIPE, DM3-RIPN]
------------------------------------------------------------------------
*** Dmitry Morozovsky --- D.Marck --- Wild Woozle --- marck@rinet.ru ***
------------------------------------------------------------------------

Re: fallback authentication

From
Ron Peterson
Date:
On Sat, Dec 11, 2004 at 11:43:08PM -0600, Bruno Wolff III wrote:
> On Sat, Dec 11, 2004 at 22:55:55 -0500,
>   Ron Peterson <rpeterso@mtholyoke.edu> wrote:
> >
> > I have:
> >
> > host    all         all         127.0.0.1         255.255.255.255   md5
> > host    all         all         127.0.0.1         255.255.255.255   pam postgresql
> > host    all         all         0.0.0.0           0.0.0.0           reject
> >
> > I've also tried reversing the first two lines.  Either strategy
> > individually works, but I'd like lookups which don't work locally to try
> > pam (or vice-versa).  What am I missing?
>
> You can't use 'all' for the username specification. You need to explicitly
> list out the usernames in the first host line. (Which should be the md5
> line.)

Thanks.  Exactly what I was hoping for.

--
Ron Peterson
Network & Systems Manager
Mount Holyoke College
http://www.mtholyoke.edu/~rpeterso

Re: i need help

From
Simon Riggs
Date:
On Sun, 2004-12-12 at 05:33, Philip Michael D Vargas wrote:
> Good day to all ...
>
> I hope any one can give me an advice ... to optimize my database...
>
> im having a problem when i'm backing up my DB and also using vacuum for my
> DB...
> my CPU load goes up.... and no one can use my DB... most of the transaction
> comes from web server..
>
> I just need ur good advice ...

Consider increasing shared_buffers, but consider what your RAM is before
you do that.

You'll need to give reasonable details if you want good help. The
specific details are important in knowing what might be causing your
problem.

There is much good advice available already and the manuals are good
too...

--
Best Regards, Simon Riggs


Re: i need help

From
"Philip Michael D Vargas"
Date:
Oh..

Sorry about the details

I'm using a ASUS machine with dual processor... 4gb memory...

Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/sdb1              3526172   1132784   2214268  34% /
/dev/sda1               248895      8796    227249   4% /boot
none                   2005700         0   2005700   0% /dev/shm
/dev/md0              65757260  52334548  10082408  84% /var
/dev/sdc1             17409840  12740248   3785216  78% /backup

thank you for your reply...

----- Original Message -----
From: "Simon Riggs" <simon@2ndquadrant.com>
To: "Philip Michael D Vargas" <pmdv@comclark.com>
Cc: <pgsql-admin@postgresql.org>
Sent: Wednesday, December 15, 2004 7:24 AM
Subject: Re: [ADMIN] i need help


> On Sun, 2004-12-12 at 05:33, Philip Michael D Vargas wrote:
> > Good day to all ...
> >
> > I hope any one can give me an advice ... to optimize my database...
> >
> > im having a problem when i'm backing up my DB and also using vacuum for
my
> > DB...
> > my CPU load goes up.... and no one can use my DB... most of the
transaction
> > comes from web server..
> >
> > I just need ur good advice ...
>
> Consider increasing shared_buffers, but consider what your RAM is before
> you do that.
>
> You'll need to give reasonable details if you want good help. The
> specific details are important in knowing what might be causing your
> problem.
>
> There is much good advice available already and the manuals are good
> too...
>
> --
> Best Regards, Simon Riggs
>
>