PostgreSQL and user restrictive view with functions involved

In PostgreSQL it’s quite easy to restrict access for user to some tables:

  1. create restrictive view
  2. grant usage on view schema
  3. grant select on view to restricted user
  4. done

Really easy. And it’s not working when the restrictive view, is selecting from another view that is using function(s) ! In that case, you might get very informative error:

ERROR:  permission denied for relation <TABLE>

or

ERROR:  permission denied for schema <SCHEMA>

Why ? Well everything works as expected, you have permission to SELECT from that restrictive VIEW and thus you really have some access to the underlying view/table, but function used in that view is still executed with permission of restricted user and therefore you obviously end up with ‘permission denied‘.

Solution for this is simple, force affected function(s) to execute with privileges of user that created it:

ALTER FUNCTION <FUNCTION> SECURITY DEFINER;

See CREATE FUNCTION manual for more info.

Using log-malloc2 for unit testing memory allocations

Latest version of log-malloc2 library provides (IMHO) unique little feature, that makes it well suited for unit testing memory allocations. It provides simple API for inquiring actual memory usage at runtime. This way, it is possible to compare usage before entering and after leaving some function, to ensure that there are no memory leaks inside of it.

Continue reading

Printing backtrace on SIGSEGV

New version of log-malloc2 provides new helpful functions and scripts that make backtrace printing and analyzing easy and convenient.

log-malloc2_util.h provides few fully inlined functions:

1. log_malloc_backtrace_init(void)

  • Pre-initializes backtrace() function, to avoid any later memory allocations. Use of this function is optional, but it’s good to use it on program start if you want to generate backtrace in SIGSEGV signal handler (memory allocations in SIGSEGV should be avoided if possible).

2. ssize_t log_malloc_backtrace(int fd)

  • Prints current backtrace to given file descriptor, including process memory map (/proc/self/maps) to make backtrace symbol conversion easier (this is needed because of ASLR).
  • Generated output can be directly pasted to backtrace2line script, that will convert it to human readable stack trace (ASLR is supported).

Because both functions are inlined, it is not needed to link program against log-malloc2 library, and this makes it also bit easier to use it in segfault (SIGSEGV) signal handler.

Simple XSLT ifnull for numbers

Answer to question how to display zero instead of NaN in XSLT for non existing node containing number values (kind of ifnull or coallesce functions that are available in SQL).

You can do it by standard expressive XSLT way, with using variable and <xsl:choose>, or abuse built-in sum() function and do whole thing in one line.

Standard way:

<!– read the value –>
<xsl:variable name=”val”>
<xsl:choose>
<xsl:when test=”//number[1]”><xsl:value-of select=”//number[1]”/></xsl:when>
<xsl:otherwise>0</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<!– print the value out –>
<xsl:value-of select=”$val“/>

 

Quick way:

<!– read and printout –>
<xsl:value-of select=”sum(//number[1])“/>

 

Both codes will print value of first node named number or zero if the node is not present.  Because it is a sum() function, it’s a good idea to limit nodeset only to first one, otherwise you will get a sum of all existing number nodes.

Btw. do you know the best XSLT reference out there ? No ? Look at ZVON XSLT reference.