Обсуждение: pg_hba.conf file
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
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
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.
Thanks for your help.
Jodi
_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu
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 > > > > > > > > > > > > > > > > > > > > >
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