PostgreSQL – Granting Access To All Views

Yet another request came to me today for granting SELECT permissions. This time, the request was to give the user the correct privileges to use a view. Basically, you can use the same format that is used for tables as is used for views:

SELECT 'GRANT SELECT ON ' || quote_ident(schemaname) || '.' || quote_ident(viewname) || ' TO john_doe;'
FROM pg_views
WHERE schemaname = 'public';

After running a query like this one, you will be given all of the GRANT statements needed to give SELECT permissions to all of the views in the public schema.

PostgreSQL – Escape Regular Expressions

Someone had an issue today where they were using a string as if it were a regular expression. Not totally understanding what the desired end result would be, I had suggested they use something equivalent to Java’s Pattern.quote() function. It turned out that they really just wanted to determine if one string was equivalent to another, but the issue still made me wonder: is there an equivalent in PostgreSQL which gives you the ability to get a literal regular expression pattern string for any string? I unfortunately didn’t find anything in the documentation pages for PostgreSQL so I wrote the following function definition:

  LANGUAGE plpgsql
AS $$
 * Function Name: regexp_quote
 * In-coming Param:
 *   The string to decoded and convert into a set of text arrays.
 * Returns:
 *   This function produces a TEXT that can be used as a regular expression
 *   pattern that would match the input as if it were a literal pattern.
 * Description:
 *   Takes in a TEXT in and escapes all of the necessary characters so that
 *   the output can be used as a regular expression to match the input as if
 *   it were a literal pattern.
 * Created On: 2012-07-10
 * Updated On: 2012-07-10
 * Author: Chris West
  RETURN REGEXP_REPLACE($1, '([[\\](){}.+*^$|\\\\?-])', '\\\\\\1', 'g');

This function can be in the following way:

SELECT regexp_quote('[]{}'); -- produces "\\[\\]\\{\\}"

You really don’t have to define a function if you don’t want to. You could just use the following form to do the equivalent:

-- produce "\\[\\]\\{\\}"
SELECT REGEXP_REPLACE('[]{}', '([[\\](){}.+*^$|\\\\?-])', '\\\\\\1', 'g');

PostgreSQL – Granting Access To All Schemas

Another DBA task that I seem to have to carry out is making sure that a specified user or group has all (CREATE & USAGE) permissions to all of the schemas (except pg_catalog for which they only get USAGE) in the database.  For this reason, I end up using the following SELECT statement to generate all of the needed GRANT statements:

    || CASE schemaname WHEN 'pg_catalog' THEN 'USAGE' ELSE 'ALL' END
    || ' ON SCHEMA ' || quote_ident(schemaname) || ' TO john_doe;'
FROM pg_tables;

The above query can be modified in whatever way that you like so that it can create a GRANT statement such as is outlined on the corresponding PostgreSQL 8.1+ documentation page.  Of course, there may be a better way (such as using a table that has only one row for each schema name). If you have another way to grant privileges to a schema, please let me know.