The PostgreSQL permissions machinery is a pretty powerful thing. It allows people to come up with wonderful permission systems. In addition to that it is still reasonably simple to use. However, there are always some corner cases, which are often forgotten or simple not discovered by end users.
In PostgreSQL a role can inherit permissions from another role. The INHERIT keywords ensure that permissions are passed on from role to role. This works perfectly and it is widely accepted by end users.
However, there is a little catch. Consider the following example:
|
1 2 3 4 5 6 7 8 |
test=# CREATE ROLE x NOLOGIN INHERIT SUPERUSER; CREATE ROLE test=# CREATE ROLE y LOGIN INHERIT NOSUPERUSER; CREATE ROLE test=# GRANT x TO y; GRANT ROLE |
In this case two roles are created. One role is marked as superuser and the other role is just an ordinary user. So far there is nothing special.
Let's create a simple table:
|
1 2 |
test=# CREATE TABLE t_test (id int); CREATE TABLE |
Now let us see, what INHERIT does. “x” is set to NOLOGIN so “x” cannot connect to the database:
|
1 2 |
[hs@localhost pgtest]$ psql test -U x psql: FATAL: role 'x' is not permitted to log in |
“y” can log into the database nicely because it has permissions to do so:
|
1 2 3 |
[hs@localhost pgtest]$ psql test -U y psql (9.5rc1) Type 'help' for help. |
Again, no surprises. However, it is surprising that “y” is not able to drop a table. Remember, “x” (= superuser) has been granted to “y” before. In PostgreSQL SUPERUSER, CREATEDB, and CREATEROLE are never inherited. In other words: Somebody can inherit from a superuser without actually being able to act as a superuser later on:
|
1 2 |
test=> DROP TABLE t_test; ERROR: must be owner of relation t_test |
However, there is SET ROLE: A user may decide to act as a different role granted to him:
|
1 2 |
test=> SET ROLE x; SET |
In this case we see that the table can indeed be dropped:
|
1 2 |
test=# DROP TABLE t_test; DROP TABLE |
By “downgrading” himself it can actually happen that there are more permissions available than before.
For more info on permissions/ privileges, see the following blogs:
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.
UPDATED July 2023: The PostgreSQL optimizer is really a wonderful piece of software which is capable of doing great things. One of those great things is so-called “join pruning”. In case PostgreSQL detects a join which is actually not needed for execution, it will automatically remove it from the plan. Removing joins from the plan can result in significantly better performance and provide end users with much simpler plans.
Two tables are needed:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
test=# CREATE TABLE a (aid int PRIMARY KEY); CREATE TABLE test=# INSERT INTO a VALUES (1), (2), (3); INSERT 0 3 test=# CREATE TABLE b (bid int PRIMARY KEY); CREATE TABLE test=# INSERT INTO b VALUES (2), (3), (4); INSERT 0 3 |
Mind that both tables have a primary key - which will play a major role later on.
To see what PostgreSQL does, we can write a basic query:
|
1 2 3 4 5 6 7 8 |
test=# SELECT * FROM a LEFT JOIN b ON (aid = bid); aid | bid -----+----- 1 | 2 | 2 3 | 3 (3 rows) |
This is a left-join, which takes all values from the left and matches values on the right. Here's the plan:
|
1 2 3 4 5 6 7 8 9 10 |
test=# explain SELECT * FROM a LEFT JOIN b ON (aid = bid); QUERY PLAN ----------------------------------------------------------------- Hash Left Join (cost=67.38..137.94 rows=2550 width=8) Hash Cond: (a.aid = b.bid) -> Seq Scan on a (cost=0.00..35.50 rows=2550 width=4) -> Hash (cost=35.50..35.50 rows=2550 width=4) -> Seq Scan on b (cost=0.00..35.50 rows=2550 width=4) (5 rows) |
PostgreSQL will perform a hash join to produce the final result. In the SELECT-clause, a star will ensure that all columns are returned.
However, what happens if only columns from the first table are needed?
|
1 2 3 4 5 6 |
test=# explain SELECT a.* FROM a LEFT JOIN b ON (aid = bid); QUERY PLAN ----------------------------------------------------- Seq Scan on a (cost=0.00..35.50 rows=2550 width=4) (1 row) |
In this case, PostgreSQL can prune the plan and just do a sequential scan on “a”.
Why is that possible? Well, first of all no information from “b” is needed to satisfy the SELECT-clause. However, this is not a sufficient condition to justify eliminating the table from the execution plan. Both sides must be unique. Remember, in a 1:n relationship the join could potentially return more data than a query without the join would. So, only if both sides are unique is it logically possible to just skip the second table. If you can prove that a simpler plan will produce the same result, you can use the simpler plan.
Read more about joins and performance:
There have been two patches since this blog post was originally written that use the table definition to improve row count estimates or take shortcuts:
PostgreSQL 9.6 has added the following:
Use foreign key relationships to infer selectivity for join predicates (Tomas Vondra, David Rowley)
Here also is the improved version of this commit.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.
I think I first touched PostgreSQL in 1998 or 1999. It has been a long time, but PostgreSQL keeps inspiring me. I still keep finding cool features which have most likely been around for 15 years or more. Recently, I noticed by coincidence that psql can produce perfect LaTeX code. How cool is that? It's so cool that I decided to share my happiness with people reading my blog.
Here's some test data:
|
1 2 3 4 5 6 7 8 9 10 |
test=# CREATE TABLE cool_feature (a int, b int); CREATE TABLE test=# INSERT INTO cool_feature VALUES (1, 2), (3, 4); INSERT 0 2 test=# TABLE cool_feature; a | b ---+--- 1 | 2 3 | 4 (2 rows) |
Making psql produce LaTeX is really simple:
|
1 2 |
test=# pset format latex Output format is latex. |
From now on the output will be returned in LaTeX format:
|
1 2 3 4 5 6 7 8 9 |
test=# TABLE cool_feature; begin{tabular}{r | r} textit{a} & textit{b} \ hline 1 & 2 \ 3 & 4 \ end{tabular} noindent (2 rows) \ |
This is even true for backslash commands:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
test=# \d begin{center} List of relations end{center} begin{tabular}{l | l | l | l} textit{Schema} & textit{Name} & textit{Type} & textit{Owner} \ hline public & cool_feature & table & hs \ public & pg_stat_statements & view & hs \ public & pgbench_accounts & table & hs \ public & pgbench_branches & table & hs \ public & pgbench_history & table & hs \ public & pgbench_tellers & table & hs \ end{tabular} noindent (6 rows) \ |
PostgreSQL can also produce a variety of other formats, which might be useful to some people: aligned, asciidoc, html, latex, latex-longtable, troff-ms, unaligned, and wrapped.
Find out more about cool psql tricks - read Julian Markwort's blog about using psql to automate repeated tasks: gexec in psql for poweruser practice.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.
