CHAPTER 29 SECURING POSTGRESQL GRANT SELECT,INSERT ON
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.