Обсуждение: pg_hba.conf file

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

pg_hba.conf file

От
Jodi Kanter
Дата:
My current pg_hba.conf file looks like this:
 
local         genex                         password  pgpasswords_genex
host          genex       127.0.0.1   255.255.255.255  password pgpasswords_genex
 

local        herr_lab                         password  pgpasswords_herr_lab
host         herr_lab      127.0.0.1     255.255.255.255  password pgpasswords_herr_lab
 
"genex" and "herr_lab" are two separate databases which are used by two different departments. I set my pg_hba.conf file up this way to ensure that only the logins within the "pgpasswords_genex" file could access the genex database. And similarly for the herr_lab database - I only wanted user IDs within the pgpasswords_herr_lab file to access the herr_lab database.
 
The problem here is that template1 is not mentioned and therefore commands like dropdb and createdb are not functioning. I tried adding the following lines:
 
local         template1                     password  pgpasswords_genex
local         template1                     password  pgpasswords_herr_lab
 
The problem here is that the system seems to ignore the second line. The logins within the "pgpasswords_genex" file can now create and drop databases but the users in "pgpasswords_herr_lab" cannot.
 
I would like to set it up such that only the genex users (with db creation permissions) can add or drop the genex database and only the herr_lab users (with db create permissions) can add or drop the herr_lab database.
 
Is this possible? Can I get the system to recognize both pgpasswords files when referencing template1? Is there a better way to accomplish my goal? 
I recall a message posted somewhat recently regarding the pg_passwd utility. Is there some security flaw that I need to be aware of?
Thanks for your help.
Jodi
 

_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu


 

 

 

Re: pg_hba.conf file

От
Jodi Kanter
Дата:
I thought of that. The only problem is that the users in that file
(pgpasswords_template1) can drop either database.
If I control who has that ability I shouldn't have to worry too much, but I
was hoping to restrict people to only the database they are allowed to
modify.

----- Original Message -----
From: "Oktay Altunergil" <postgres@altunergil.com>
To: "Jodi Kanter" <jkanter@virginia.edu>
Sent: Tuesday, September 03, 2002 1:33 PM
Subject: Re: [ADMIN] pg_hba.conf file


> You will probably need to create a pgpasswords_template1 file in addition
to those two you already have and add people to it manually.
>
> Oktay
>
> On Tue, 03 Sep 2002 12:43:03 -0400
> Jodi Kanter <jkanter@virginia.edu> wrote:
>
> > My current pg_hba.conf file looks like this:
> >
> > local         genex                         password  pgpasswords_genex
> > host          genex       127.0.0.1   255.255.255.255  password
pgpasswords_genex
> >
> >
> > local        herr_lab                         password
pgpasswords_herr_lab
> > host         herr_lab      127.0.0.1     255.255.255.255  password
pgpasswords_herr_lab
> >
> > "genex" and "herr_lab" are two separate databases which are used by two
different departments. I set my pg_hba.conf file up this way to ensure that
only the logins within the "pgpasswords_genex" file could access the genex
database. And similarly for the herr_lab database - I only wanted user IDs
within the pgpasswords_herr_lab file to access the herr_lab database.
> >
> > The problem here is that template1 is not mentioned and therefore
commands like dropdb and createdb are not functioning. I tried adding the
following lines:
> >
> > local         template1                     password  pgpasswords_genex
> > local         template1                     password
pgpasswords_herr_lab
> >
> > The problem here is that the system seems to ignore the second line. The
logins within the "pgpasswords_genex" file can now create and drop databases
but the users in "pgpasswords_herr_lab" cannot.
> >
> > I would like to set it up such that only the genex users (with db
creation permissions) can add or drop the genex database and only the
herr_lab users (with db create permissions) can add or drop the herr_lab
database.
> >
> > Is this possible? Can I get the system to recognize both pgpasswords
files when referencing template1? Is there a better way to accomplish my
goal?
> > I recall a message posted somewhat recently regarding the pg_passwd
utility. Is there some security flaw that I need to be aware of?
> > Thanks for your help.
> > Jodi
> >
> >
> > _______________________________
> > Jodi L Kanter
> > BioInformatics Database Administrator
> > University of Virginia
> > (434) 924-2846
> > jkanter@virginia.edu
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
>


Re: pg_hba.conf file

От
Bruce Momjian
Дата:
7.3, due out in a few months, has several solutions to this. First, you
can list username directly in pg_hba.conf, and filenames containing
username, and lists of files containing usernames, so you can have
@file1,@file2.  I don't know a way to specify multiple filenames in
7.2.X.

I am attaching the new pg_hba.conf contents so you can see what is
possible and comment on how it meets your needs.

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

Jodi Kanter wrote:
> My current pg_hba.conf file looks like this:
>
> local         genex                         password  pgpasswords_genex
> host          genex       127.0.0.1   255.255.255.255  password pgpasswords_genex
>
>
> local        herr_lab                         password  pgpasswords_herr_lab
> host         herr_lab      127.0.0.1     255.255.255.255  password pgpasswords_herr_lab
>
> "genex" and "herr_lab" are two separate databases which are used by two different departments. I set my pg_hba.conf
fileup this way to ensure that only the logins within the "pgpasswords_genex" file could access the genex database. And
similarlyfor the herr_lab database - I only wanted user IDs within the pgpasswords_herr_lab file to access the herr_lab
database.
>
> The problem here is that template1 is not mentioned and therefore commands like dropdb and createdb are not
functioning.I tried adding the following lines: 
>
> local         template1                     password  pgpasswords_genex
> local         template1                     password  pgpasswords_herr_lab
>
> The problem here is that the system seems to ignore the second line. The logins within the "pgpasswords_genex" file
cannow create and drop databases but the users in "pgpasswords_herr_lab" cannot.  
>
> I would like to set it up such that only the genex users (with db creation permissions) can add or drop the genex
databaseand only the herr_lab users (with db create permissions) can add or drop the herr_lab database. 
>
> Is this possible? Can I get the system to recognize both pgpasswords files when referencing template1? Is there a
betterway to accomplish my goal?  
> I recall a message posted somewhat recently regarding the pg_passwd utility. Is there some security flaw that I need
tobe aware of? 
> Thanks for your help.
> Jodi
>
>
> _______________________________
> Jodi L Kanter
> BioInformatics Database Administrator
> University of Virginia
> (434) 924-2846
> jkanter@virginia.edu
>
>
>
>
>
>
>
>
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
#
#          PostgreSQL HOST-BASED ACCESS (HBA) CONTROL FILE
#
#
# This file controls:
#     o which hosts are allowed to connect
#     o how users are authenticated on each host
#     o databases accessible by each host
#
# It is read on postmaster startup and when the postmaster receives a SIGHUP.
# If you edit the file on a running system, you have to SIGHUP the postmaster
# for the changes to take effect, or use "pg_ctl reload".
#
# Each line is a new record. Records cannot span multiple lines.
# Comments begin with # and continue to the end of the line.
# Blank lines are ignored. A record consists of tokens separated by
# spaces or tabs.
#
# Each record specifies a connection type and authentication method. Most
# records also can restrict based on database name or IP address.
#
# When reading this file, the postmaster finds the first record that
# matches the connection type, client address, and database name, and uses
# that record to perform client authentication. If no record matches, the
# connection is rejected.
#
# The first token of a record indicates the connection type. The
# remainder of the record is interpreted based on that type.
#
# Record Types
# ============
#
# There are three record types:
#     o host
#     o hostssl
#     o local
#
# host
# ----
#
# This record identifies hosts that are permitted to connect via TCP/IP.
#
# Format:
#
#   host       DATABASE    USER      IP_ADDRESS    MASK               AUTH_TYPE
#
# DATABASE can be:
#    o a database name
#    o "sameuser", which means a user can only access a database with the
#      same name as their user name
#    o "samegroup", which means a user can only access databases when they
#      are members of a group with the same name as the database name
#    o "all", which matches all databases
#    o a list of database names, separated by commas
#    o a file name containing database names, starting with '@'
#
# USER can be:
#    o a user name
#    o "all", which matches all users
#    o a list of user names, separated by commas
#    o a group name, starting with '+'
#    o a file name containing user names, starting with '@'
#
# Files read using '@' can contain comma-separated database/user names,
# or one name per line.  The files can also contain comments using '#'.
#
# IP_ADDRESS and MASK are standard dotted decimal IP address and
# mask values. IP addresses can only be specified numerically, not as
# domain or host names.
#
# Do not prevent the superuser from accessing the template1 database.
# Various utility commands need access to template1.
#
# AUTH_TYPE is described below.
#
#
# hostssl
# -------
#
# The format of this record is identical to "host".
#
# It specifies hosts that require connection via secure SSL. "host"
# allows SSL connections too, but "hostssl" requires SSL-secured
# connections.
#
# This keyword is only available if the server was compiled with SSL
# support.
#
#
# local
# -----
#
# This record identifies the authentication for local UNIX domain socket
# connections. Without this record, UNIX-socket connections are disallowed
#
# Format:
#   local      DATABASE    USER      AUTH_TYPE
#
# This format is identical to the "host" record type except there are no
# IP_ADDRESS and MASK fields.
#
#
#
# Authentication Types (AUTH_TYPE)
# ================================
#
# AUTH_TYPE indicates the method used to authenticate users. Each record
# has an AUTH_TYPE.
#
#   trust:
#        No authentication is done. Any valid user name is accepted,
#         including the PostgreSQL superuser. This option should
#         be used only for hosts where all users are trusted.
#
#   md5:
#          Requires the client to supply an MD5 encrypted password for
#        authentication.  This is the only method that allows encrypted
#        passwords to be stored in pg_shadow.
#
#   crypt:
#          Same as "md5", but uses crypt for pre-7.2 clients.
#
#   password:
#        Same as "md5", but the password is sent in cleartext over
#        the network.  This should not be used on untrusted
#        networks.
#
#   ident:
#        For TCP/IP connections, authentication is done by contacting the
#        ident server on the client host. This is only as secure as the
#        client machine. You must specify the map name after the 'ident'
#        keyword. It determines how to map remote user names to
#        PostgreSQL user names. If you use "sameuser", the user names are
#        assumed to be identical. If not, the map name is looked up
#        in the $PGDATA/pg_ident.conf file. The connection is accepted if
#        that file contains an entry for this map name with the
#        ident-supplied username and the requested PostgreSQL username.
#
#        On machines that support unix-domain socket credentials
#        (currently Linux, FreeBSD, NetBSD, and BSD/OS), ident allows
#        reliable authentication of 'local' connections without ident
#        running on the local machine.
#
#   krb4:
#        Kerberos V4 authentication is used.  Allowed only for
#        TCP/IP connections, not for local UNIX-domain sockets.
#
#   krb5:
#        Kerberos V5 authentication is used.  Allowed only for
#        TCP/IP connections, not for local UNIX-domain sockets.
#
#   pam:
#        Authentication is done by PAM using the default service name
#        "postgresql". You can specify your own service name by adding
#        the service name after the 'pam' keyword. To use this option,
#        PostgreSQL must be configured --with-pam.
#
#   reject:
#         Reject the connection. This is used to reject certain hosts
#        that are part of a network specified later in the file.
#        To be effective, "reject" must appear before the later
#        entries.
#
#
#
# Examples
# ========
#
#
# Allow any user on the local system to connect to any database under any
# username using Unix-domain sockets (the default for local connections):
#
# TYPE       DATABASE    USER       IP_ADDRESS    MASK               AUTH_TYPE
# local      all         all                                         trust
#
# The same using local loopback TCP/IP connections:
#
# TYPE      DATABASE     USER    IP_ADDRESS    MASK               AUTH_TYPE
# host      all          all     127.0.0.1     255.255.255.255    trust
#
# Allow any user from any host with IP address 192.168.93.x to
# connect to database "template1" as the same username that ident reports
# for the connection (typically his Unix username):
#
# TYPE       DATABASE    USER    IP_ADDRESS    MASK               AUTH_TYPE
# host       template1   all     192.168.93.0  255.255.255.0      ident sameuser
#
# Allow a user from host 192.168.12.10 to connect to database "template1"
# if the user's password is correctly supplied:
#
# TYPE       DATABASE    USER     IP_ADDRESS    MASK               AUTH_TYPE
# host       template1   all      192.168.12.10 255.255.255.255    md5
#
# In the absence of preceding "host" lines, these two lines will reject
# all connection from 192.168.54.1 (since that entry will be matched
# first), but allow Kerberos V5 connections from anywhere else on the
# Internet. The zero mask means that no bits of the host IP address are
# considered so it matches any host:
#
#
# TYPE       DATABASE    USER     IP_ADDRESS    MASK               AUTH_TYPE
# host       all         all      192.168.54.1  255.255.255.255    reject
# host       all         all      0.0.0.0       0.0.0.0            krb5
#
# Allow users from 192.168.x.x hosts to connect to any database if they
# pass the ident check. For example, if ident says the user is "james" and
# he requests to connect as PostgreSQL user "guest", the connection is
# allowed if there is an entry in $PGDATA/pg_ident.conf with map name
# "phoenix" that says "james" is allowed to connect as "guest":
# See $PGDATA/pg_ident.conf for more information on Ident maps.
#
# TYPE       DATABASE    USER     IP_ADDRESS    MASK               AUTH_TYPE
# host       all         all      192.168.0.0    255.255.0.0       ident phoenix
#
# If these are the only three lines for local connections, they will
# allow local users to connect only to their own databases (databases
# with the same name as their user name) except for administrators and
# members of group 'support' who may connect to all databases . The file
# $PGDATA/admins contains a list of user names. Passwords are required in
# all cases.
#
# TYPE       DATABASE    USER      IP_ADDRESS    MASK               AUTH_TYPE
# local      sameuser    all                                        md5
# local      all         @admins                                    md5
# local      all         +support                                   md5
#
# The last two lines above can be combined into a single line:
#
# local      all         @admins,+support                           md5
#
# The database column can also use lists and file names, but not groups:
#
# local      db1,db2,@demodbs  all                                  md5
#
#
#
#
#
#
# Put your actual configuration here
# ==================================
#
# The default configuration allows any local user to connect using any
# PostgreSQL username, including the superuser, over either UNIX domain
# sockets or TCP/IP.
#
# If you want to allow non-local connections, you need to add more "host"
# records. Also, remember TCP/IP connections are only enabled if you
# start the postmaster with the -i flag, or enable "tcpip_socket" in
# $PGDATA/postgresql.conf.
#
# CAUTION: if you are on a multiple-user machine, the default
# configuration is probably too liberal for you. Change it to use
# something other than "trust" authentication.
#
# TYPE       DATABASE      USER      IP_ADDRESS    MASK               AUTH_TYPE

local        all           all                                        trust
host         all           all       127.0.0.1     255.255.255.255    trust