Jsp, Tomcat, J2Ee, Linux Web Hosting, Hibernate Programming Blog

CHAPTER 29 SECURING POSTGRESQL Summary An uninvited database intrusion can wipe away months of work and erase inestimable value. Therefore, although the topics covered in this chapter generally lack the glamour of other feats, such as creating a database connection or altering a table s structure, the importance of taking the time to thoroughly understand them cannot be understated. We strongly recommend that you take adequate time to understand PostgreSQL s security features, because they should be making a regular appearance in all of your PostgreSQL-driven applications. In the next chapter, we ll take a look at PHP s PostgreSQL library, showing you how to manipulate the PostgreSQL database data through your PHP scripts.
From our experience, we are can tell you that you can find a reliable and cheap webhost service at Java Web Hosting services.

662 CHAPTER 29 SECURING POSTGRESQL Configuration Options Once your server has been built with SSL support, PostgreSQL can listen for SSL connections. To enable this, you must turn on SSL by setting the ssl option to true in the postgresql.conf file, and then restart your server. By default, the server leaves it to the client s discretion to decide whether to use an SSL connection, which may or may not be what you prefer. You can change this behavior in the pg_hba.conffile through one of the following host connection types: host: This is the default connection type. It allows both SSL and non-SSL connections, and leaves the connection method to the client. Since some clients may silently fall back on non-SSL connections, you may not want to use this connection type if you need to enforce SSL connections. hostssl: Connections specified with the hostssl connection type will be required to connect using SSL, and non-SSL connection attempts will be rejected even if all other credentials would allow a connection. If you plan to use SSL, this is most likely the connection type you would want. hostnossl: Requires that connections be made from a non-SSL-based client. Connections made over SSL will be rejected even if all other credentials would allow a connection. Frequently Asked Questions Because the SSL feature is not widely used, there is still some confusion surrounding its usage. This section attempts to offer some clarifications by answering some of the most commonly asked questions regarding this topic. I m using PostgreSQL solely as a back end to my Web application, and I am using HTTPS to encrypt traffic to and from the site. Do I need to encrypt the connection to the PostgreSQL server? This depends on whether the database server is located on the same machine as the Web server. If this is the case, then encryption will likely be beneficial only if you consider the machine itself insecure. If the database resides on a separate server, then the data could potentially be traveling unsecured from the Web server to the database server, and therefore it would warrant encryption. There is no steadfast rule regarding the use of encryption. You can reach a conclu sion only after a careful weighing of security and performance factors. I understand that encrypting Web pages using SSL will degrade performance. Does the same hold true for the encryption of PostgreSQL traffic? Yes, your application will take a performance hit, because every data packet must be encrypted while traveling to and from the PostgreSQL server. How much of a hit will depend on a number of variables, including CPU speed and bandwidth capacity. How do I know that the traffic is indeed encrypted? The easiest way to ensure that the PostgreSQL traffic is encrypted is to configure a user account that requires SSL connections, and then try to connect to the SSL-enabled PostgreSQL server by supplying that user s credentials and a valid SSL certificate. If something is awry, you will receive a FATAL error when you attempt to connect. What port does PostgreSQL use for SSL-based traffic? The port number remains the same regardless of whether you are communicating in encrypted or unencrypted fashion. By default, this port is port 5432.
We highly recommend you visit web and email hosting services if you need stable and cheap web hosting platform for your web applications.

CHAPTER 29 SECURING POSTGRESQL GRANT SELECT,INSERT ON books, games TO howard, robert WITH GRANT OPTION; REVOKE Removing privileges from a user is the job of the REVOKE command. Its syntax is similar to that of the GRANT command: REVOKE privilege [, …] ON object [, …] FROM {PUBLIC | GROUP groupname | username } For example, if we want to disallow any use of the salaries table by howard, we would use the following command: REVOKE ALL ON salaries FROM howard; Making Widespread Changes A situation that you are likely to encounter often is one where you want to grant to a user permissions on all tables within a given database, with a single command, without making the user a superuser. By default, PostgreSQL does not provide this ability, because it goes against the SQL standard. However, if you want to allow such granting of permissions to occur, a workaround is to use database functions. Since Chapter 32 discusses functions in more detail, we won t get into the gory details here, but the basic idea is to pass in a username, select all the table names within the database into a record, and then loop through the record, executing a GRANT (or REVOKE) statement for each table. Secure PostgreSQL Connections Data flowing between a client and a PostgreSQL server is similar to any other typical network traffic; it could potentially be intercepted and even modified by a malicious third party. Sometimes this isn t really an issue, because the database server and clients often reside on the same internal network and, for many, on the same machine. However, if your project requirements result in the transfer of data over insecure channels, you now have the option to use PostgreSQL s built-in security features to encrypt the connection using SSL. To use SSL-based connections, you first must do the following: Install the OpenSSL library, available for download at http://www.openssl.org/. Compile PostgreSQL with the with-openssl flag. To verify that your PostgreSQL installation has been built with OpenSSL, you can use the pg_configure command-line tool: [postgres@ridley postgres]$ pg_config –configure ‘ prefix=/var/lib/pgsql-8.0.x’ ‘ with-openssl’ Once these prerequisites are complete, you need to either create or purchase both a server and a client certificate. The process for accomplishing either of these tasks is beyond the scope of this book, but you can get information about this process on the Internet, so take a few moments to perform a search and you ll turn up numerous resources.
If you are looking for affordable and reliable webhost to host and run your business application visit our ftp web hosting services.

660 CHAPTER 29 SECURING POSTGRESQL Deleting Groups To remove a group, we use the DROP GROUP command: DROP GROUP groupname DROP GROUP removes the named group, although any users contained within the group will remain. Note PostgreSQL 8.1 will introduce role support, based on the outline found in the SQL standards. Role support will further expand on the USER and GROUP feature set, and promises to be a powerful addition to the PostgreSQL toolset. In some scenarios, using roles will be preferred over the current user and group functions; however, the current user and group functions will remain, so don t be worried that you will have to adjust for a whole new set of commands right away. Still, you ll want to check out the online documentation once 8.1 is released. The GRANT and REVOKE Commands Once users have been created within the system, the task of adding or removing privileges requires use of the GRANTand REVOKE commands. Since privileges are set at the object level, this allows for a high level of granularity for each user in the database. In this section, we take a look at the GRANT and REVOKE commands in detail and walk through a number of examples demonstrating their usage. GRANT You use the GRANT command when you need to assign new privileges to a user or group of users. The privilege assignment is done on a per-object basis, and uses slightly different syntax depending on the object and privilege in question, but follows the same basic structure in all cases: GRANT privilege [, …] ON object [, …] TO {PUBLIC | GROUP groupname | username } [ WITH GRANT OPTION ] The privilege can be one or more privileges appropriate to the object in question. Likewise, the object can be one or more like objects to grant privileges on. The keyword PUBLIC signifies that all users will be granted the privileges. By default, only object owners and superusers can grant permissions on an object; however, the WITHGRANTOPTION passes on these privileges, so that the grantee can then grant said privileges upon others if desired. To better see how these commands come together, let s take a look at a few examples. In our first example, we want to add SELECT privileges on the table salaries to user howard: GRANT SELECT ON salaries TO howard; This is pretty straightforward. For a more complex example, let s say we want to add SELECT and INSERT privileges on the booksand games tables to both howardand robert and allow them to grant those privileges to others:
Check Tomcat Web Hosting services for best quality webspace to host your web application.

CHAPTER 29 SECURING POSTGRESQL DROP USER username The DROP USER command eliminates the user from any and all databases within a cluster. If the user owns a database, an error will be raised and the user will not be deleted. The same is not true of other objects within a database, though. Dropping the user will leave any such objects within the database intact. However, you might end up with permission issues in the future should you need to manipulate the object in some way that requires you to be the object s owner. Working with PostgreSQL Groups While PostgreSQL s user system is flexible, it isn t always the most convenient system to work with when you are dealing with a large number of users and privileges. To help ease this task, PostgreSQL also provides a group system, similar to the group concept used in many operating systems. With groups, you can assign a number of users to a group, set permissions at the group level, and then manipulate these privileges for all users in a single go. Adding Groups Adding new groups to PostgreSQL is accomplished through the CREATE GROUP command, which has the following syntax: CREATE GROUP groupname [ WITH ] SYSID gid | USER username [, …] As with the CREATE USER command, the recommended practice is to leave the SYSID option blank so that it will be auto-generated. The USER field, which is optional, can contain one or more users. For example, if we wanted to create a group for users with full access, the command would look like this: CREATE GROUP fullaccess WITH USER howard, rob; Manipulating Groups When creating a group, it may not always be feasible to add all users into a group. We may be unsure of which users need to be members of a group, and over time new users will be added into the database after our group is created. In contrast to this, we will surely also have a need to remove users from groups as our database evolves. To accomplish these tasks, we use the ALTER GROUP command: ALTER GROUP groupname ADD USER username [,…] ALTER GROUP groupname DROP USER username [,…] There is also a form of the ALTER GROUP command for renaming groups: ALTER GROUP groupname RENAME TO newgroupname In all cases, these ALTER GROUP commands can be executed only by a database superuser.
We recommend you use shared web hosting services, because many users agree that it is cheap, reliable and customer-satisfying webhost.

658 CHAPTER 29 SECURING POSTGRESQL Adding New Users Adding new users to PostgreSQL is accomplished through the CREATE USER command. The CREATE USER command has the following syntax: CREATE USER username [ WITH SYSID uid | CREATEDB | NOCREATEDB | CREATEUSER | NOCREATEUSER | IN GROUP groupname [, …] | [ ENCRYPTED | UNENCRYPTED ] PASSWORD ‘password’ | VALID UNTIL ‘abstime’ ] The recommended practice is to leave the SYSID field blank, so that it will be autogenerated for you. The CREATEDB field corresponds to allowing the user to create, add, and drop databases within the database; by default, users do not get this privilege. Specifying the CREATEUSER option will create the user as an administrative-level account, allowing them to add and remove other users from the system; again, the default is to not give this privilege. You can also add the user to any groups you might have in the database, via the INGROUP parameter. Of course, you will normally want to store a password for each user as well. Finally, the VALIDUNTIL clause allows you to specify a time in which the account will expire automatically and disallow further logins. As an example, we might create the following user howard, who has permissions to create new databases, and will be able to log in until the end of the year: CREATE USER howard WITH PASSWORD ‘T3rc35′ CREATEDB VALID UNTIL ‘2005-12-31′; Manipulating Users To modify the attributes of a user, we use the ALTER USER command. Its syntax looks like: ALTER USER username [ WITH CREATEDB | NOCREATEDB | CREATEUSER | NOCREATEUSER | [ ENCRYPTED | UNENCRYPTED ] PASSWORD ‘password’ | VALID UNTIL ‘abstime’ The parameters to the ALTER USER command follow the same definitions as those of the CREATE USER command. For example, if we wanted to modify our previous user to remove the create database privileges, it would look like this: ALTER USER howard NOCREATEDB; Sometimes you may need to change the user s name, in which case the alternate syntax is provided: ALTER USER name RENAME TO newname Removing Users To remove a user, we use the DROP USER command. Its syntax is very straightforward:
Looking for affordable and reliable webhost to host and run your business application? Then look no more and go to servlet web hosting services.

CHAPTER 29 SECURING POSTGRESQL w: Stands for write and represents UPDATE privileges. d: Stands for delete and represents DELETE privileges. R: Stands for rule and allows the user to create or drop rules on the given relation. x: For the REFERENCES privilege. Users with this privilege can create foreign keys from other tables that reference the relations in question. t: For the TRIGGER privilege. Users with this privilege can create and drop triggers on the given relation. An entry within the relacl column comprises one or more of the preceding attributes preceded with user information to create a complete privilege entry. If the user portion is left blank, the privileges listed are granted to PUBLIC, or all, users. In later versions of PostgreSQL, these entries are followed by a /username portion that signifies who granted the permissions in the entry. Let s take a look at a few examples: The first example demonstrates SELECT, INSERT, and UPDATEprivileges for user rob, granted by user dylan: rob=raU/dylan The next example shows SELECT privileges for PUBLIC, granted by the Postgres superuser: =r/postgres Finally, this example demonstrates full privileges for user dylan, granted by user dylan, and INSERT and UPDATE privileges for PUBLIC, granted by user dylan: {dylan=arwdRxt/dylan,=aw/dylan} Note The owner of an object gets full privileges by default. However, these privileges are not displayed in the relacl column by default. Instead, they become visible only when they have been explicitly granted by someone. User and Privilege Management While the privilege information can be read from the pg_class table just like any other table in PostgreSQL, for the purposes of manipulating it, you would not want to have to construct cumbersome arrays to update those values. Instead, PostgreSQL supports several SQL commands that you can use to add, update, and drop users, groups, and the various privileges those users might need. Working with PostgreSQL Users PostgreSQL gives us several SQL-level commands to create users and groups, thus defining their roles within the database system: CREATE USER, ALTER USER, and DROP USER for manipulating users, and CREATE GROUP, ALTER GROUP, and DROP GROUP for manipulating groups.
Visit our web design programs services for an affordable and reliable webhost to suit all your needs.

656 CHAPTER 29 SECURING POSTGRESQL # Allow any client with IP address 192.168.76.x to connect to the # “warehouse” database as user “reports” as long as a password is # given # TYPE DATABASE USER CIDR-ADDRESS METHOD host warehouse reports 192.168.76.0/24 password # Allow user “rob” from host 192.168.21.12 to connect to database # “template1″ if the user’s password is correctly supplied. # # TYPE DATABASE USER CIDR-ADDRESS METHOD host all rob 192.168.21.12/32 md5 # Allow connection from any IP address on the Internet to connect to # either the bpsimple or bpfinal databases, provided that the user can # pass an ident check for being either rick or neil # TYPE DATABASE USER CIDR-ADDRESS METHOD host bpsimple,bpfinal rick,neil 0.0.0.0/0 ident The pg_class Table Once a user has authenticated through the pg_hba.conf file, the next step of the connection is to determine whether the user is authorized to execute a given query. This duty falls primarily on information found in the pg_class table. The pg_class table contains a wide array of information about most of the different table-like objects in a PostgreSQL database, including tables, views, and indexes, but for the purposes of securing your database, the key column in this table is called relacl, which can be thought of as the relations access control list. The relacl column is rather cryptic at first glance, but its information can be deduced with a little direction. The relacl column s data type is an array of aclitems, which is quite different from any other column you might have seen. A typical relacl entry might look something like this: phppg=# SELECT relname, relacl FROM pg_class WHERE relname=’pg_class’; relname | relacl ———-+———pg_ class | {=r/postgres} (1 row) This means that the user postgres has granted read permissions on the table pg_class to PUBLIC. But this is getting a little ahead of ourselves, so let s take a moment to break down the different types of permissions that are available to users and what their corresponding entries would be. The list of attributes you will find in the reacl column includes the following items: a: Stands for append and represents INSERT privileges. r: Stands for read and represents SELECT privileges.
If you are looking for affordable and reliable webhost to host and run your business application visit our ftp web hosting services.

CHAPTER 29 SECURING POSTGRESQL METHOD: Specifies the authentication method that applies to the specified connection rule. Several different authentication methods are available. Only the most common methods are listed here, but you can consult the online documentation for more information: trust: Allows connections for the specified rule to connect without any type of authentication or verification of the user or their password. This method is not recommended for production machines. password: Requires that a password be supplied for any connecting user. The password will be sent in plain text over the connection, so it is often recommended that this method should be used only in connection with some type of SSL arrangement. md5: Requires the connecting user to supply an MD5-encrypted password for authentication. Note that even though the password is encrypted, the connection still sends the hash via plain text, so it is not immune to sniffing-based attacks. While md5 is generally preferred over the password method, it too is best used in conjunction with some type of SSL connection. krb5: Uses Kerberos 5 to authenticate the user. This requires an external Kerberos key file and is available only for TCP/IP-based connections. pam: Authenticates the user via the Pluggable Authentication Modules service available from the operating system. ident: Authenticates users based on the connecting client s username, as determined by the operating system. You can create an optional identmap file if you want certain operating system users to be able to connect as different database users. Note that ident is not generally recommended as an authorization protocol, and therefore should be used only on machines on which the client can be well-secured. reject: Automatically rejects any connection matching the specified rule. This can sometimes be useful for filtering out certain connections from a larger group. The order in which each row is placed in the pg_hba.conf is significant because PostgreSQL will authenticate incoming connections based on the first available match it finds within the file. For this reason, you will usually find that earlier entries will have strict connection-matching parameters along with weaker authentication methods, followed by more wide-reaching connection-matching parameters alongside tougher authentication methods. A typical pg_hba.conf might look something like this: # Allow users on the local system to connect to any database under # any username using Unix domain sockets # TYPE DATABASE USER CIDR-ADDRESS METHOD local all all trust # Implement the same permissions as above, but for connections on # local loopback TCP/IP connections. (i.e. localhost) # TYPE DATABASE USER CIDR-ADDRESS METHOD host all all 127.0.0.1/32 trust
If you are searching for cheap webhost for your web application, please visit MySQL5 Web Hosting services.

654 CHAPTER 29 SECURING POSTGRESQL The pg_hba.conf File Client authentication is controlled by the pg_hba.conf file, which is typically found in the data directory of the PostgreSQL server. By default, the pg_hba.conf file is set to allow connections from the local machine only, but it gives you the flexibility to handle extremely complex connection requirements. The basic format of pg_hba.conf is a list of single-line entries, with each entry containing a number of fields separated by tabs or spaces. Each line in the file represents an allowed connection, based on several different specified parameters. In this section, we take a more detailed look at each of the parts of a pg_hba.conf entry: TYPE: Describes the type of connection: local: Can only be made on the local Unix socket. host: Made via TCP/IP. You must also specify an address for PostgreSQL to listen on via the listen_addresses variable in the postgresql.conf file for TCP/IP connections to work. hostssl and hostnossl: Variants of the host connection that are used in conjunction with SSL connectivity; these are discussed later in this chapter. DATABASE: Specifies which database or databases the user is allowed to connect to. Multiple databases can be specified with a comma-separated list of database names. You can also use one of several keywords for further options: all: Signifies that the user can connect to all databases in the system. sameuser: Means that the user can only connect to a database with the same name as the user connecting. samegroup: Signifies that the user must belong to the group with the same name as the database they are attempting to connect to. USER: Specifies which user or users the specified connection rule applies to. Multiple users can be specified by using a comma-separated list of usernames. To use a group name, you should append a + to the name of the group. You can also use the keyword all to have the rule apply to all users. CIDR-ADDRESS: Specifies which client machines the given connection rule applies to. The format is that of a numeric IP address followed by a valid CIDR mask length (e.g., 192.168.21.12/32). Note that bits to the right of the CIDR mask must be zero, and there cannot be any white space between the IP address, the /, and the mask. For example, if you wanted anyone on your local subnet to be able to connect, you would write the entry as 172.21.1.0/24. This field applies only to TCP/IP-based connection types. IP-ADDRESS + IP-MASK: As an alternative to the CIDR-ADDRESS notation, you can use separate IP-ADDRESS and IP-MASK entries. Using this notation, our example would look like 172.21.1.0 for the IP-ADDRESS field and 255.255.255.0 for the mask. Like the CIDR-ADDRESS notation, these fields apply only to TCP/IP-based connection types.
We recommend high quality webhost to host and run your jsp application: christian web host services.