How To Check PostgreSQL Privileges

The Problem

Someone asked me recently how to list all tables that a given user can select from. The problem they had was that they had a special user used by a client, so the client can make its own sql queries. However due to the security policy, that user can only perform SELECT queries, and only from some tables.

They wanted to give the client a list of tables which can be selected.

Continue reading

PostgreSQL FOUND Problem

FOUND is a global variable that exists in the plpgsql procedural language used in PostgreSQL database. Last time I was writing some complicated procedures for moving many records to archive schema, just for having only the fresh data the main schema so it would be faster. During that I notices a very strange error that suddenly turned out just to be a stupid mistake.
The manual says that:

A SELECT INTO statement sets FOUND true if a row is assigned, false if no row is returned.

So I used it… that way, but not with a simple query, I used `execute` for some custom string created by some parameters concatenation, nevermind. Here is a very simple test sql that shows the problem:
Continue reading