From Xu Yongjun published on January 31, 02:16. To be honest, my LAMP website is actually can't forget this elephant. MySQL is a brisk dolphin, but lacks a number of modern relational databases, such as reference integrity, view, trigger, etc. Therefore, if you need to develop an e-commerce website, if you need these features, you may consider PostgreSQL. This article will briefly introduce the usage by its installation process on Red Hat 7.1. PostgreSQL's official download address is: ftp: //ftp.postgreSQL.org/pub/v7.1.3/postgreSQL-7.1.3.tar.gzhttp://www.postgreSQL.org/ If you download the latest development version, you need Download and install the Flex (version number greater than 2.5.4) and Bison (version number greater than 1.28) Designers For security reasons, PostgreSQL cannot run with root users, so you must establish a corresponding user and group.
# UserAdd Postgre (Automatically establish Postgre Group) The process is not complex and other source version of the installation method Similar: Decompression to / usr / local / src: # tar xvfz postgreSQL-7.1.3.tar.gz # cd PostgreSQL-7.1 .3 # ./configure --prefix = / usr / local / pgsql # make # make install # chown -r postgre.postgre / usr / local / pgsql This is not all good, there are some ending work Do: # vi ~ postgre / .bash_profile Add: pglib = / usr / local / pgsql / libpGData = HOME / DATAPATH = $ PATH: / usr / local / pgsql / binmanpath = $ manpath: / usr / local / pgsql / manexport PGLIB PGDATA PATH MANPATH postgres user login, # su - postgre establish a database directory: $ mkdir data start the database engine: $ initdb [postgre @ www postgre] $ initdbThis database system will be initialized with username "postgre" .This user will own all the data files and must also own the server process.Fixing permissions on pre-existing data directory / home / postgre / dataCreating database system directory / home / postgre / data / baseCreating database XLOG directory / home / postgre / data / pg_xlogCreating template database in / home / postgre / data / base / template1creating global relations in / home / postgre / data / baseadding template1 Database to pg_databaseCreating view pg_user.Creating view pg_rules.Creating view pg_views.Creating view pg_tables.Creating view pg_indexes.Loading pg_description.Vacuuming database.Success You can now start the database server using:. / usr / local / pgsql / bin / postmaster -D / Home / Postgre / DataR / USR / local / pgsql / bin / pg_ctl -d / home / Postgre / Data Start $ Postmaster -i -d ~ / data & [1] 22603 [postgre @
WWW Postgre] $ debug: Data Base System Is Starting Up at Thu Jan 31 02:00:44 2002Debug: Data Base System Was Shut Down At Thu Jan 31 01:57:58 2002DEBUG: DATA Base System Is in Production State At Thu Jan 31 02:00:44 2002 This PostgreSQL uses a database located in / usr / local / pgsql / data, allowing Internet users to connect (-i) and run in the background. Creating a database $ createDB MyDB PostgreSQL will return information about "Created Database", indicating that the database is completed. $ PSQL MyDB Enters Interaction PSQL Tools, establishes table: CREATE TABLE MyTable (ID VARCHAR (20), Name Varchar (30)); After the establishment is complete, it will receive a "create" information, which means establishing success. Now insert a data: Insert Into MyTable Values; PSQL Returns INSERT 18732 1, Query Insert Successful: SELECT * from myTable; Exit PSQL, with / Q command. To make the online other machines can be accessed to modify the following
$ vi /home/postgre/data/pg_hba.conf Add to Local All Trust # Allowed through the local all machine host all 0.0.0.0 0.0.0.0 Trust # Allow NORMAN HOST All 192.168.0.96 255.255.255.255 Trust # Allow the network 192.168.0.96 machine $ vi /home/postgre/data/postgreSql.conf#tcpip_socket = false Modified to TCPIP_SOCKET = TRUE
$ PG_CTL Start # Start Database $ PG_CTL RESTART # Restart Database $ PG_CTL Stop # Close Database
================================================================================★
The access authentication configuration mainly involves two main profiles: postgreSQL.conf and pg_hba.conf.
Postgresql.conf unix_socket_group: Sets the group owner of UNIX domain set, (Socket ownership users always start POSTMASTER users) Use the UNIX_Socket_Permissions option to add additional access control mechanisms to this socket type. By default, it is a empty string, which is the use of the default group of the current user. This option can only be set when the server starts.
Unix_socket_permissions: Set access to UNIX domain sets, Unix domain sockets use the usual UNIX file system rights set. An optional value can be a digital mode that can be accepted by a CHMOD and UMASK system call. (To use a customized octal format, the number must be 0777 at 0 (zero), i.e. any person can join, reasonable parties may be 0770 (see Unix_Socket_Group) and 0700 (only users). (Please note that for UNIX sockets, it is actually only written permission, and there is no way to set or unread or execute permission) This option can only be set when the server is started. Pg_hba.conf is the main file set up to access authentication, the format is a row for each record, and each line specifies an access authentication. Set an access authentication contains 7 parts: Connection mode, database (Database), User Name, IP Address (IP-Address), Subnet Mask (IP-MASK), Authentication Method (Authentication) Method, authentication-option, the following is a detailed description of these 7 parts: connection mode: connection method has three types: local, host, hostssl local, this record matches through UNIX domain socket The joint attempt, there is no record of this type, will not allow the Unix domain socket connection. Host This record matches the connection attempt to pass through the TCP / IP network, please note that the TCP / IP connection is prohibited from being prohibited unless the server is started with the -i option or open the TCPIP_Socket configuration parameter set inside postgreSQL.conf. of. HostSSL This record matches the SSL join at the SSL on TCP / IP, and the Host record can match the SSL and non-SSL coupling attempts, but the HostSSL record requires an SSL join. Database: Declare the database that matches the database. Value ALL indicates that the record matches all databases, and the value Sameuser indicates that if the requested database and the requested user are the same name, it matches. SameGroup indicates that the user must be a member of the group that is the same as the database. In other cases, this is the name of a specific PostgreSQL. We can declare multiple databases with a comma-separated method. A file containing the database name can be declared by prefix the file prefix. This file must be in the same directory with pg_hba.conf.
User Name: The POSTGRESQL user that matches this record declared, the value all indicates that it matches all users. Otherwise, it is the name of a specific PostgreSQL user, and multiple usernames can be declared by a comma-separated method, group names can be declared by using group name preruption. A file containing the username can be declared by prefix @ in front of the file name, the file must be in the same directory.
IP Address (IP-Address) and subnet mask (IP-MASK): The two fields contain the IP address / mask value represented by the standard point decision. (IP addresses can only be declared in a digital manner, and the domain name or host name can not be placed together, declare the IP address of this record that matches the client. Accurate logic is: (actual-ip-address xor ip-address-field) and ip-mask-field For records to match the records to match. If the connection method is Host or HostSSL, these two must be specified, otherwise you can not fill.
Authentication Method: Trust unconditionally allows for links, which allows any users who can connect with the PostgreSQL database to join any of their expected PostgreSQL database users without a password. The REJECT connection is unconditional rejection, which is often used to "filter" some hosts from a group. The MD5 requires the client to provide a MD5 encrypted password for authentication, which is the only way to allow the encryption port to store in pg_shadow. Crypt is similar to the MD5 method, just use old CRYPT encryption certifications, used for 7.2, for 7.2 and later clients, we recommend using MD5. Password and "MD5" are the same, but the password is passed on the web in a clear text, and we should not use this way in an unsafe network. KRB4 authenticated users with Kerberos V4, only when the TCP / IP connection is performed. (Translation: Kerberos, "Kerpolos", so Greek myths Plut Handas's multi-head watch dog. Keberos is the certification protocol and / or key exchange method for the base and symmetric encryption algorithm developed by MIT. It is characterized. Two different uses of servers are required, one for authentication identity, a key exchange for both users of the channel. At the same time, Kerberos is relatively high to prevent playback attacks, so usually with NTP services.) KRB5 Kerberos V5 authenticated users. Only when the TCP / IP join is performed. (Translation: Kerberos V5 is the improvement of the above V4, mainly no longer relying on the DES algorithm, and adds some new features.) Ident gets the customer's operating system name (for TCP / IP connection, the user's identity is through and running in customer The Ident server connection on the end is determined. For the local connection, it is obtained from the operating system.) Then check it, see if the user allows the user to join the required database user, the method is to declare it behind the Ident keyword. Mapping. If you use the SameUser mapping, then assume that the username is equal. If this keyword is not declared, the map name is found in the $ pgdata / pg_ident.conf file. If this file contains a mapping of the POSTGRESQL user name identified by the username and the request provided by Ident, the connection is accepted. For local join, only can be used if the system supports UNIX domain sets (currently Linux, FreeBSD, NetBSD, and BSD / OS). PAM is authenticated using the pluggable authentication modules (PAM) provided by the operating system.
1. Whether in the local or through TCP / IP, anyone can access my database to use the identity of any of him to access my database local all trust host all 0.0.0 0.0.0.0 Trust2, all access is used. MD5 authentication method Local all md5 host all 0.0.0.0 0.0.0.0 md5 3, PGSQL user's authentication method must be trust is not password, and other MD5 authentication Local Pgsql Trust local all md5 host all 0.0.0.0 0.0.0.0 MD5 4 In the subsequent detection, I found that although it is protected by the network, if anyone can use the UNIX domain socket, anyone can also use the `psql -u pgsql -d template1` to easily enter My database, this is obvious, not what I want, now I need unix_socket_permissions assistance, when I set this item to 0700, only the owner of the socket, that is, the system user PGSQL can use sockets Connection, if there are several administrators who need the highest permissions, 0770 is also a choice, but I only have one administrator, so I chose 0700, now my pg_hba.conf change to: Local Pgsql Trust Host ALL 0.0.0.0 0.0.0.0 MD5 After unix_socket_permissions, only the system user PGSQL can have no restrictions (of course, great root exceptions), and I don't have to run to the terminal before you start to enter a password. And others can only connect through the network, even if they log in to the local computer, other users can only connect to the) PSQL -U XX -D Template1 -H 127.0.0.1`, this connection is password authentication. Protected, this is all what I want. . ########################################################################################## PG_CTL START [-W] [-s] [-d DataDir] [-l filename] [-o options] [-p path] pg_ctl stop [-w] [-ss] [-d datadir] [-MS [Mart ] | f [AST] | i [mmediate]] pg_ctl restart [-w] [-s] [-d datadir] [-ms [mart] | f [AST] | i [mmediate]] [-O Options] PG_CTL Reload [-s] PG_CTL STATUS [-D DATADIR]
