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

652 CHAPTER 29 SECURING POSTGRESQL developers have relieved you of these tasks, integrating fully featured authentication and authorization capabilities into the server. How the Privilege System Works The PostgreSQL privilege system revolves around two general concepts: Authentication: Determines whether a user is even allowed to connect to the server Authorization: Determines whether the user posses adequate privileges to execute query requests Because authorization cannot take place without successful authentication, you can think of this process as taking place in two stages. The Two Stages of Access Control The general privilege control process takes place in two steps: connection authentication and request verification. Together, these steps are carried out in the following phases: 1. The postmaster compares the connection request information against the entries in the pg_hba.conf file to determine whether the connection should be accepted or rejected. This is done by matching different variables, including the user, connecting host, and database involved. 2. The postmaster verifies any password information against the appropriate location based on the authentication type specified in pg_hba.conf. For authentication types like password, this means verifying the user and password against the pg_shadow table. 3. If the request makes it to Step 3, the postmaster parses and analyzes the query itself to determine which objects within the database the user is attempting to interact with, and in what way. The postmaster then looks up the permissions for these objects in the various pg_ system tables, such as pg_classor pg_proc. If all permissions have been granted appropriately, the query is then executed. Where Is Access Information Stored? PostgreSQL access authorization information is stored in two places: the pg_shadow system table and the pg_hba.conf system file. The pg_shadow table holds the information for specific database user accounts, along with password information and some system-level privilege information. The pg_hba.conf file controls which users can connect to which databases from which machines. Once authenticated, PostgreSQL keeps user authorization information stored primarily in the relacl column of the pg_class table. In this section, we will delve into the details pertinent to the purpose and structure of each of these parts. The pg_shadow Table The pg_shadow table contains detailed information about PostgreSQL users. It controls various system-level privileges and password information for database users. Looking at the pg_shadow table through the psql program, you see the following:
From our experience, we are can tell you that you can find a reliable and cheap webhost service at Java Web Hosting services.

CHAPTER 29 SECURING POSTGRESQL template1=# ALTER USER postgres PASSWORD ’secret’; ALTER USER template1=# Of course, you should choose a password that is a tad more complicated than secret. PostgreSQL will let you dig your own grave in the sense that passwords such as 123, abc, and your cat s name are perfectly acceptable. However, common security practices suggest choosing a password that is at least eight characters in length, and consists of a mixture of numeric and alphabetical characters of varying case. Securing the PostgreSQL Daemon When you start the postmaster process, several options are available that you can take advantage of to help secure your installation. The first place to look for these options is in the postgresql.conf file. This file also contains settings that are unrelated to security, but in this section, we focus on those related to maintaining good server security: listen_address: Specifies the TCP/IP address or addresses that PostgreSQL listens on for client connections. The default setting is localhost, which means that the installation will listen for connections only from the local machine, via TCP/IP or Unix-domain sockets. Setting the list to empty causes the server to ignore any IP interfaces and connect only via Unix-domain sockets. If your database and application reside on the same machine, you should definitely consider setting the list to empty. port: Sets the port number that PostgreSQL will accept the connection on. Setting this value to something other than the default (5432) can help dodge malicious attempts at scanning for the PostgreSQL service; just be sure to choose a port that is not commonly used for something else. ssl: Enables SSL connections. While configuration of SSL can be tricky, and its use will incur some overhead, it is essential for applications that need to transmit critical data. krb_server_keyfile: Sets the location of the Kerberos server key file. It is only needed when using Kerberos-based authentication, but it does give you another option for secured connections. The PostgreSQL Access Privilege System Protecting your data from unwarranted review, modification, or deletion, accidental or otherwise, should always be your primary concern. Yet balancing a secure database with an expected level of user convenience and flexibility is often a difficult affair. The delicacy of this balance becomes obvious when you consider the wide array of access scenarios that might exist in any given environment. For example, what if a user requires modification privileges, but not insertion privileges? How do you authenticate a user who might need to access the database from a number of different IP addresses? What if you wanted to provide a user with read access to only certain tables, while restricting the rest? You can imagine the nightmarish code that might result from incorporating such features into the application logic. Thankfully, the PostgreSQL
In case you need quality webspace to host and run your web applications, try our personal web hosting services.

650 CHAPTER 29 SECURING POSTGRESQL Patch the operating system and any installed software: Software security alerts seem to be issued on a weekly basis these days, and although they are annoying, it is absolutely necessary that you take the steps to make sure that your system is fully patched. With exploit instructions and tools readily available on the Internet, a malicious user with even a little experience in such matters will have little trouble taking advantage of an unpatched server. Don t take solace in the fact that you are running a Unix-based environment; every operating system has had at least one security patch released, and pretending otherwise could leave you vulnerable. The bottom line is that you should develop an official patching strategy and stick with it, regardless of your chosen operating system. Disable all unused system services: Always take care to eliminate all unnecessary potential server attack routes before you place the PostgreSQL server on the network. These attack vectors are almost exclusively the result of insecure system services, often ones running on the system unbeknownst to the system administrator. The rule of thumb these days is that if you re not going to use the service, turn it off. Tighten the database server firewall: Although shutting off unused system services is a great way to lessen the probability of a successful attack, it doesn t hurt to add a second layer of security by closing all unused ports. For a dedicated database server, it is common to close all ports except for 22 (SSH), 5432 (PostgreSQL), and perhaps some utility ports like 123 (NTP). In short, if you don t intend for traffic to travel on a given port, close it off altogether. In addition to making such adjustments on a dedicated firewall appliance or router, also consider taking advantage of the operating system s firewall. Both Microsoft Windows Server 2000/2003 and Unix-based systems have built-in firewalls at your disposal. Audit the database server s user accounts: Particularly if a pre-existing server has been reassigned to host the organization s database, make sure that all nonprivileged users are disabled or, better, deleted. Although PostgreSQL s users and the operating system users are completely unrelated, the mere fact that they have access to the server environment raises the possibility that damage could be done, inadvertently or otherwise, to the database server and its contents. The simplest way to ensure that nothing is overlooked during such an audit is to reformat all of the attached drives and reinstall the operating system. Set the PostgreSQL superuser password: By default, many installation packages leave the database superuser account (postgres) blank. Although many would question this practice, this has long been the standard procedure, and will likely be for some time. Given that fact, you must take care to add a password immediately. You can do so with the ALTERUSER command, like so: $] psql -U postgres template1 Welcome to psql 8.0.3, the PostgreSQL interactive terminal. Type: copyright for distribution terms h for help with SQL commands ? for help on internal slash commands g or terminate with semicolon to execute query q to quit
We would like to recommend you tested and proved virtual web hosting services, which you will surely find to be of great quality.

CHAPTER 29 Securing PostgreSQL When you park your car at the store, you likely take a moment to lock the doors and set the alarm system, if you have one. It s almost an automatic reaction, because you know that the possibility of the car or its contents being stolen dramatically increases if you don t take such basic yet effective precautions. Ironically, the IT industry at large seems to take the opposite approach when creating the vehicles used to maintain enterprise data. Both IT systems and applications are rife with open doors, leading to theft of customer data, damage, and even destruction as a result of electronic attacks. Often such occurrences take place not because the technology did not offer deterrent features, but simply because the implementer never bothered to put these deterrents into effect. This chapter introduces numerous aspects of PostgreSQL s highly effective security model. In particular, it describes PostgreSQL s user system in detail, showing you how to create users and groups, manage their privileges, and change their passwords. Additionally, this chapter demonstrates some of PostgreSQL s secure connection features. While no amount of discussion will force you to implement these features, hopefully the examples and anecdotes interspersed throughout this chapter will be enough to convince the majority of readers to take the time to do so. After completing this chapter, you should be familiar with the following topics: What steps you should take immediately after starting PostgreSQL for the first time Securing the postmaster process (postgresql.conf) PostgreSQL s host-based authentication system The GRANT and REVOKE functions User account management, including working with groups Creating secure connections with SSL Let s start at the beginning: what you should do before doing anything else with your PostgreSQL server. What You Should Do First This section outlines several rudimentary, yet very important, tasks that you should undertake immediately after completing the installation and configuration process outlined in Chapter 25:
If you are looking for cheap and quality webhost to host and run your website check Jboss Web Hosting services.

CHAPTER 28 FROM DATABASES TO DATATYPES ALTER DOMAIN us_phone_number SET NOT NULL; This combination would change the owner of the domain to a database user named amber: ALTER DOMAIN us_phone_number OWNER TO amber; Dropping Domains You can remove a domain from the database by using the DROPDOMAIN command. By default, you cannot drop a domain that is referenced inside another database object. However, you can control this behavior by using the CASCADE or RESTRICT keyword along with the DROP command: DROP DOMAIN us_phone_number CASCADE; Note The DROP CASCADE command may have different effects depending on the dependent object. For example, if a table references the domain, only the column in question will be dropped. However, if a view references the domain, the entire view will be dropped. Summary In this chapter, you learned about the many ingredients that go into designing a PostgreSQL database. The chapter began with an overview of some helpful commands for dealing with databases, schemas, and tables. This discussion was followed by an introduction to PostgreSQL s supported datatypes, offering information about the name, purpose, and range of each. The chapter then examined many of the most commonly used attributes, which serve to further tweak column behavior. The chapter concluded with a discussion of how to make use of more advanced datatype objects, including composite datatypes and domains, to help simplify datatype management. In the next chapter, we ll dive into another key PostgreSQL feature: security. You ll learn all about PostgreSQL s powerful authentication system, as well as learn more about how to secure the PostgreSQL server and create secure PostgreSQL connections using SSL.
Check Tomcat Web Hosting services for best quality webspace to host your web application.

646 CHAPTER 28 FROM DATABASES TO DATATYPES this sounds complicated, it isn t especially, and domains can be quite useful when applied properly. One good example is handling phone numbers. Many databases have a phone number column in several of their tables, which then requires each table to set up its own constraints to handle the data. Rather than go through that hassle, you could instead create a domain to handle phone numbers and then use that in all of your tables. Creating Domains Domains are created by using the CREATEDOMAIN command. Domains generally comprise a set of attributes, CHECK, DEFAULT, NOTNULL, or NULL, that behave like other datatype attributes within PostgreSQL. In this example, we set up a domain to match a valid U.S. phone number, which we define as starting with 1, followed by a dash, three numbers, another dash, three more numbers, a third dash, and then four numbers: CREATE DOMAIN us_phone_number AS TEXT CONSTRAINT “valid_phone_number” CHECK (VALUE ~ ‘^1-\d{3}-\d{3}-\d{4}$’); CREATE TABLE us_contact_info ( fullname TEXT NOT NULL, email TEXT NOT NULL, phone us_phone_number NOT NULL ); As you can see, writing the regular expression once in the domain is much simpler than writing this expression several times in multiple tables. This also gives us one place to change should we need to modify our phone number definition. Altering Domains You can use the ALTERDOMAIN command to modify any aspect of a domain s definition. Each form of the ALTERDOMAINcommand takes the form of ALTERDOMAINdomain_name followed by one of the following subforms: { SET DEFAULT expression | DROP DEFAULT }: Sets expression as the default value or drops the existing default value. { SET | DROP } NOT NULL: Controls whether or not the domain allows NULL values. ADD domain_constraint: Adds a new constraint to the domain using the same syntax as the CREATEDOMAIN command. It will succeed only if all values in an existing column satisfy the new constraint. DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]: Drops constraints on a domain. OWNER TO new_owner: Changes the ownership of the domain. Using these commands should be fairly straightforward, but just to make sure, let s walk through a few examples. This command would forbid someone from entering NULL values into our DOMAIN:
Please visit our professional web hosting services to find out about cheap and reliable webhost service that will surely answer all your demands.

CHAPTER 28 FROM DATABASES TO DATATYPES company=# CREATE TABLE contacts (employee_id integer, im im_accounts); CREATE TABLE Next, we insert some data into our table. Note that the syntax for inserting into a composite type simply involves encapsulating the various pieces of information that make up the field within parentheses: company=# INSERT INTO contacts (employee_id, im) company-# VALUES (1,(’bigceo@jabber.org’,'thebigceo’,'bigceo76′)); INSERT 0 1 And finally, for good measure, let s take a look at our data: company=# SELECT * FROM contacts; employee_id | im ————-+—————————————1 | (bigceo@jabber.org,thebigceo,bigceo76) (1 rows) Altering Composite Types The ALTERTYPE command can be used to change the definition of an existing composite type. In versions prior to PostgreSQL 8.1, this is limited to changing the owner of the type: ALTER TYPE im_accounts OWNER TO amber; Starting in 8.1, PostgreSQL also gives you the ability to alter the schema of a given type: ALTER TYPE im_accounts SET SCHEMA mynewschema; Dropping Composite Types Dropping a composite type is done through the DROPTYPE command. By default, you cannot drop a composite type that is referenced by any other objects. This can be controlled by using the CASCADE or RESTRICT keywords, and can be schema-qualified if needed: DROP TYPE mynewschema.im_accounts CASCADE; Note The DROP CASCADE command may have different effects depending on the dependent object. For example, if a table references the composite type, only the column in question will be dropped. However, if a view references the composite type, the entire view will be dropped. Working with Domains Domains can be considered a cross between a datatype and a constraint. Creating a domain generally requires two pieces of information: the underlying base type that the domain will use, and the constraint limiting the acceptable values for the domain. While you might think
In case you need affordable webhost to host your website, our recommendation is ecommerce web host services.

644 CHAPTER 28 FROM DATABASES TO DATATYPES related tables can be left alone. We can also create foreign keys between tables based on a group of columns between the two tables. We will re-create our staff table again to show the syntax: CREATE TABLE staff ( staffid SERIAL NOT NULL PRIMARY KEY, email TEXT NOT NULL, lname TEXT, nickname TEXT, FOREIGN KEY (lname,nickname) REFERENCES friends(lname,nickname) ); This syntax sets up the relationship just like our previous example; any entry in staff must now have a corresponding entry, based on both the lname and fname columns, in the friends table. UNIQUE A column assigned the UNIQUE attribute ensures that all values possess distinct values, except that NULL values are repeatable. You typically designate a column as UNIQUE to ensure that all fields within that column are distinct for example, to prevent the same e-mail address from being inserted into a newsletter subscriber table multiple times, while at the same time acknowledging that the field could potentially be empty (NULL). An example of a column designated as UNIQUE follows: email TEXT UNIQUE Composite Datatypes A composite datatype defines the structure of a row or record. In simple terms, it is a list of field names and their datatypes. Once a composite type is created, it can be used much like any other datatype, such as when defining a column in a table or declaring a return type for a function. This can prove very useful when you want to tightly couple related information together into a single logical piece. Creating Composite Types You can use the CREATETYPEcommand to create composite types. As shown next, the syntax is similar to that of the CREATETABLE command, though only field names and datatypes can be specified. No constraints or default values can be included. CREATE TYPE im_accounts AS ( jabber text, aim text, irc text ); Let s run through a quick example so that you can see exactly how this works. First, we create a table in which to use our new composite type:
You need excellent and relaible webhost company to host your web applications? Then pay a visit to Inexpensive Web Hosting services.

CHAPTER 28 FROM DATABASES TO DATATYPES Creating an automatically incrementing primary key: CREATE TABLE staff ( staffid SERIAL NOT NULL PRIMARY KEY, fname TEXT NOT NULL, lname TEXT NOT NULL, email TEXT NOT NULL ); Creating a single-field primary key: CREATE TABLE citizen ( ssid VARCHAR(9) NOT NULL PRIMARY KEY, fname TEXT NOT NULL, lname TEXT NOT NULL, zipcode VARCHAR(10) NOT NULL ); Creating a multiple-field primary key: CREATE TABLE friend ( fname TEXT NOT NULL, lname TEXT NOT NULL, nickname TEXT NOT NULL, PRIMARY KEY(lname, nickname) ); REFERENCES The REFERENCES attribute specifies that the values in a column (or group of columns) must match the values appearing in some row of another table. This is done to ensure referential integrity between the two tables. As an example, we could rewrite the staff table in our previous example to the following: CREATE TABLE staff ( staffid SERIAL NOT NULL PRIMARY KEY, ssid VARCHAR(9) REFERENCES citizen (ssid), email TEXT NOT NULL ); Created this way, it is now impossible to add an entry to the staff table that does not have a corresponding entry in the citizen table. While some would say this approach to staffing might be short-sighted in today s global economy, opponents of illegal immigration would surely applaud this design. This relationship between the two tables is often referred to as a foreign key (no pun intended), and it provides other benefits as well. You ll notice that we eliminated the fnameand lname columns from our table; we did this because we can now infer this information from the relationship between the two tables. This also means that, should someone s name change (for example, when someone gets married), we do not have to write extra application code to propagate the changes throughout our database: the change can be made in one place and all
We highly recommend you visit web and email hosting services if you need stable and cheap web hosting platform for your web applications.

642 CHAPTER 28 FROM DATABASES TO DATATYPES You can view the results of these entries easily enough: rob=# SELECT * FROM default_now_example; attempt | insert_time ———+——————————a | 2005-10-16 15:41:39.382608-05 b | 1492-01-13 21:12:00-05 | 2005-10-16 15:42:17.860467-05 rows) As you can see, in our first INSERT statement, the default time was entered because we passed in the DEFAULT keyword. In the second, the time we specified was entered. In the third, an autogenerated time was inserted because we did not specify a value; this is the same behavior as using the DEFAULT keyword. NOT NULL Defining a column as NOT NULL disallows any attempt to insert a NULL value into the column. Using the NOT NULL attribute, where relevant, is always suggested, because it results in at least baseline verification that all necessary values have been passed to the query. An example of a NOT NULL column assignment follows: zipcode VARCHAR(10) NOT NULL NULL Simply stated, the NULL attribute means that NULL values are acceptable for the given field. This is also the default value for the field if no data is given and there is no DEFAULT attribute specified. This is the default characteristic for columns in PostgreSQL, so you will not often see it stated explicitly. PRIMARY KEY The PRIMARY KEY attribute is used to guarantee uniqueness for a given row. No values residing in a column designated as PRIMARY KEY are repeatable or nullable within that column. It s quite common to see SERIAL columns designated as a primary key, because this column doesn t necessarily have to bear any relation to the row data, other than acting as its unique identifier. However, there are two other ways for ensuring a record s uniqueness: Single-field primary keys: Typically used when a pre-existing, nonmodifiable unique identifier exists for each row entered into the database, such as a part number or social security number. Note that this key should never change once it is set. Multiple-field primary keys: Can be useful when it is not possible to guarantee uniqueness from any single field within a record. Thus, multiple fields are conjoined to ensure uniqueness. If the number of columns required to ensure uniqueness grows cumbersome, it is common practice to simply designate a SERIALinteger as the primary key, to alleviate the need to somehow generate unique identifiers with every insertion. The following three examples demonstrate creation of the auto-increment, single-field, and multiple-field primary key fields, respectively.
Please visit our professional web hosting services to find out about cheap and reliable webhost service that will surely answer all your demands.