PostgreSQL

PostgreSQL Roles are Cluster-Wide

A role in PostgreSQL is common to all databases in the cluster. This seems to be the result of a design decision made when the former user and group handling was unified under role. Follow these links for reference:

Roles, or rather those roles that are not just representing a specific user, ought instead to be an intrinsic part of the database model. Roles are defined by the kind of access they provide (read, write, etc) and by what relations(table, view, function, etc) they provide that access to. Access control is ideally managed within a database using roles rather than separately within each individual application that uses that database. So it makes sense that the access control rules (the roles and their associated permissions) would be defined alongside the definitions of the relations for which they are controlling access, any changes are then self contained. The access control model should be represented as part and parcel of the rest of the database model. Individual users (which are also represented as roles in PostgreSQL) are assigned one or more of the roles defined within each particular database model (based on the local enterprise definition of what needs they have of any particular database).

There is no sense to representing this kind of role at the cluster level as the definition of the role is associated specifically with the database where it actually controls access. In PostgreSQL, to encapsulate the full functionality of the database requires using not only the system catalog tables specific to that database but also the roles relevant to that database held in the cluster wide system catalog tables. With the exception of some special cases, like roles for cluster wide database management, this is an artificial split. Roles that are managed outside of the database model across all databases in the cluster make some sense either when there is only one database in the cluster, or when all the databases in the cluster are not independent and act together as part of one managed system. Roles (again, those that are not just being used to represent a specific user) should otherwise be defined and managed at the level of the individual database, or arguably even at the level of each schema within a database.

Below are some ideas for enhancing PostgreSQL to support database specific roles. These ideas build up from limited and simplistic solutions to more flexible and complex ones.

Approach One

This is what we currently do. We prefix every role name with the database name. So roles called customer and salesperson used in a database called orders would actually be called orders_customer and orders_salesperson. This approach relies on all owners of databases in the cluster playing ball and we need some custom handling around role DDL statements when changes are made to the database model.

Approach Two

This just puts some syntactic sugar around the first approach.

Syntax Behaviour
CREATE [DBONLY] ROLE abc Creates the role abc after first adding a prefix which is the name of the current database returning an error if the name already exists. Without the DBONLY option behaves as now. Most other options would be incompatible with the DBONLY option, for example LOGIN, CREATEDB, etc.
DROP [DBONLY] ROLE abc Drops the role abc with a prefix matching the name of the current database returning an error if the name is not found (except when IF EXISTS is used). Without the DBONLY option behaves as now.
GRANT … ON … TO [DBONLY] abc Grants the privileges to the role abc with a prefix matching the name of the current database returning an error if the name is not found. Without the DBONLY option behaves as now.
REVOKE … ON … FROM [DBONLY] abc Removes the privileges from the role abc with a prefix matching the name of the current database returning an error if the name is not found. Without DBONLY option behaves as now.
DROPDB Drops a database and all the roles in the cluster that have a prefix matching the given database name.

The cluster wide system catalog table pg_roles is still used. A role created manually with a database prefix (without using the DBONLY option) would be treated the same as if the role had been created database specific (when using the DBONLY option). Roles names still have to be unique across the cluster.

With this approach no meta data is retained that identifies a role as cluster-wide or database-specific. A pg_dump will simply dump the role name as now (including the prefix) and a pg_load would re-create it in the same way as now. The dropdb command would drop all roles that have a prefix matching the database name irrespective of how they were actually created.

The advantage of this approach is that no change is required to the mechanism that looks up roles and checks that the level of access is appropriate to the user that has those roles when relations are actually used.

However we can definitely do better while staying simple.

Approach Three

This works much as above except that instead of adding a prefix to the role name we add a column to the cluster wide pg_roles table, catalog_name, where we add the name of the database associated with the role when using the DBONLY option (left null otherwise). The advantage is that we now have the meta data preserved as to how the role was originally defined – either database-specific or cluster-wide.

This fixes various concerns with the second approach. DROPDB can be constrained to search for roles matching the database name in the catalog_name column, so cluster roles that just happen to have a matching database name prefix will be ignored. The pg_dump and pg_restore commands can use the DBONLY option in the output as necessary by checking whether the catalog_name column has a value or not. The role name lookups for GRANT and REVOKE remain specifically controlled using the DBONLY option (so as to avoid handling any complex role hierarchy semantics). Still no change is required to the mechanism that looks up roles and checks that the level of access is appropriate to the user that has those roles when relations are actually used (on the assumption that when GRANT is used the corresponding pg_roles row OID is assigned, the actual name is irrelevant). However two roles could now exist called the same, one with catalog_name empty and one (or more) with a database name in catalog_name. So there are consequences for displaying the content of system catalog tables in psql using \du or \dp for example, so the system catalog views would need to be amended in some way.

A downside is that all the roles are still held in one system catalog table for the cluster, but in practice this might not actually matter. The semantics for pg_dump need to be thought about: when dumping a specific database would want an option to also dump its own specific roles; when dumping all databases all roles should be dumped – database-specific and cluster-wide.

This approach needs few code changes and is backwards compatible.

Approach Four

This would add a per-database pg_roles system catalog table. This holds the roles specific to that database and the role names can be the same as role names in other databases or cluster-wide. Would probably need rules to handle prioritisation in name clashes with roles held in the cluster wide pg_roles table. For example CREATE ROLE would need to check both the database-specific pg_roles and cluster-wide pg_roles. Similar complexity would need to be handled in GRANT and REVOKE. Access control checks would now need to check both database-specific pg_roles and cluster-wide pg_roles, and there may be conflicts to resolve depending on how a role hierarchy is implemented. While having a database-specific pg_roles table makes semantic sense, is necessary for managing who can change which roles, and a thorough implementation of role priority/conflict handling might result in useful behaviour, there is a lot of additional complexity in the implementation.

Schema Handling

A further enhancement should allow roles to be separately defined per-schema within a database. Support could be added in some fashion by allowing schema qualification of the role name in CREATE ROLE, using public (or as defined in the schema path) by default. The implementation of this in the third approach above would be to add a schema_name column into pg_roles.

string_to_array behaviour with an empty string

So this caught me out the other day. We had a query which broke out multiple delimited strings each with one or more elements (but always the same number of elements in each) into multiple records using string_to_array and unnest. In each string any element could potentially have an empty value, but at least one string would always have values for all the elements. This meant there should always be at least one output row. So below worked as expected:

SELECT
  UNNEST(STRING_TO_ARRAY(test.s1, '|')) AS col1,
  UNNEST(STRING_TO_ARRAY(test.s2, '|')) AS col2
FROM ( VALUES (('a|b'),('1|2')) ) AS test(s1, s2);

with the following result:

 col1 | col2 
------+------
 a    | 1
 b    | 2
(2 rows)

Below worked entirely as expected as well:

SELECT
  UNNEST(STRING_TO_ARRAY(test.s1, '|')) AS col1,
  UNNEST(STRING_TO_ARRAY(test.s2, '|')) AS col2
FROM ( VALUES (('a|b'),('|')) ) AS test(s1, s2);

with the following result:

 col1 | col2 
------+------
 a    | 
 b    | 
(2 rows)

So did this when we had some data with just the minimum one element in the input:

SELECT
  UNNEST(STRING_TO_ARRAY(test.s1, '|')) AS col1, 
  UNNEST(STRING_TO_ARRAY(test.s2, '|')) AS col2
FROM ( VALUES (('a'),('1')) ) AS test(s1, s2);

with the following result:

 col1 | col2 
------+------
 a    | 1
(1 row)

But then we discovered that there was some data that was being completely lost in the query output. Turns out that this occurred when, like above there was just one element in the input, but one value was empty:

SELECT
  UNNEST(STRING_TO_ARRAY(test.s1, '|')) AS col1,
  UNNEST(STRING_TO_ARRAY(test.s2, '|')) AS col2
FROM ( VALUES (('a'),('')) ) AS test(s1, s2);

with the following result:

 col1 | col2 
------+------
(0 rows)

So after some investigation it turns out that this happens because string_to_array on an empty string returns an array with no elements rather than an array with one element which is an empty string. This does actually make sense because without any application specific context it is indeterminate whether an empty string should be treated as having no items or should itself be treated as one item. There is a very full and complete discussion of the reasoning behind reaching the decision to return an empty array:

Nevertheless we needed to fix the query. The approach I took was to force all input strings to be at least two elements by pre-pending the delimiter and then always discarding the first element:

SELECT
  UNNEST((STRING_TO_ARRAY('|' || test.s1, '|'))[2:100]) AS col1,
  UNNEST((STRING_TO_ARRAY('|' || test.s2, '|'))[2:100]) AS col2
FROM ( VALUES (('a'),('')) ) AS test(s1, s2);

producing the correct result:

 col1 | col2 
------+------
 a    | 
(1 row)

We are using PostgreSQL version 9.5 and we knew an upper bound on the number of elements so we could get away with doing [2:100] but in PostgreSQL version 9.6 you can now do [2:] to leave the upper bound unspecified and defaulting to the array length which is obviously nicer!

The behaviour of string_to_array on a zero length string is of course documented on https://www.postgresql.org/docs/9.5/static/functions-array.html but in a rather oblique way under a supplementary note documenting changes in behaviour from before PostgreSQL version 9.1 when NULL was returned instead of an empty array.

Hold the exact(ish) “CREATE VIEW” definition in the PostgreSQL System Catalog

It would be rather nice if the exact SQL definition (well almost, see below) supplied with the “create view” statement could be held in the PostgreSQL System Catalog, rather than the re-written optimised version that is currently held. This has come up before, see:

http://www.postgresql-archive.org/idea-storing-view-source-in-system-catalogs-td1987401.html
http://www.postgresql-archive.org/Preserving-the-source-code-of-views-td5775163.html

Back then this idea got stomped on for good reasons but I am not suggesting quite the same.

We would prefer PostgreSQL to store view definitions in the system catalog *AFTER* column expansion (unlike in the discussion above) but *BEFORE* internal parser rewrites. So, currently for example:

CREATE TABLE bar ( x INTEGER, y TEXT );

CREATE VIEW foo(x, y) AS
SELECT * FROM bar WHERE y IN ( ‘a’, ‘b’ ) AND x IS NOT DISTINCT FROM 0;

SELECT view_definition FROM information_schema.views
WHERE table_name = ‘foo’;

SELECT bar.x, bar.y
FROM bar
WHERE ((bar.y = ANY (ARRAY[‘a’::text, ‘b’::text])) AND (NOT (bar.x IS DISTINCT FROM 0)));
(1 row)

The column expansion and corresponding rewrite is mandatory at the time of creation (see below), but the other changes in the definition are not. They make comparing the current definition of a view as it is in a database against an externally maintained definition difficult, which is what we in particular want to be able to do.

The SQL standard (at least the ancient version I have access to) for “information_schema” seems rather open to interpretation on this issue. It says that the view definition column:

“contains a representation of the view descriptors”

What do we take “representation” to be – presumably a completely mangled internally optimised “blob” would be compliant (but otherwise useless). The spirit of the statement might be more reasonably taken to be “a human legible representation”. In that case how much rewrite should actually be allowed – to me the rewritten definition above is already getting less legible than the original, but maybe others feel different. I don’t know if it was a design decision that the parser rewrites were folded back into the stored definition or just a side effect of the implementation. Either way, if there is any additional rewriting in the future there must be a point at which the rewrites would be considered to make the stored view definition so sufficiently illegible that the spirit of the standard at least is no longer being followed – or would future rewrites be needlessly constrained by having to preserve the legibility of the stored definition?

The SQL standard is however completely explicit about column expansion:

“NOTE 13: Any implicit column references that were contained in the <query
expression> associated with the <view definition> are replaced by explicit
column references in VIEW_DEFINITION.”

The SQL standard also implies that the definition should be NULL when the length exceeds some limit (not sure what that is but I have a vague feeling from somewhere that it was 4K characters). PostgreSQL does not enforce that limit that I know of (or simply has a much higher limit) since we have some massive views that are returned entirely – and that seems very sensible to me, can’t see a point having a limit these days (except for backwards compatibility with external applications). Maybe this limit has been removed in more recent versions of the standard.

The re-written view definition is also what is output by pg_dump. The consequence of this is that any new/altered parser rewrites made in a newer version of PostgreSQL might not be automatically applied to the view when it is restored from the old versions dump file into that new version. They would however always be applied if the view was restored from a dump that used the original raw definition from the CREATE VIEW statement.

I looked at MariaDB for comparison. This does some rewriting as well – it quotes every table and column reference and adds superfluous bracketing, but does not replace IN (it does not have a comparable “NOT DISTINCT” syntax, just <=>, but I suspect it would shift an embedded NOT in similar constructs in the same way as PostgreSQL). I guess YMMV here depending on how much clever rewriting the database does. Don’t know about other databases like Oracle or SQL Server.

Arguably the same principle should be applied to any embedded SQL literal expression used in DDL operations where the parser rewrites it, e.g. in DEFAULT values for table columns or WITH clauses used in trigger conditions. No idea what the SQL standard says about these, and indeed if they are even included under the information_schema.

Storing the original CREATE VIEW statement post column expansion is obviously of no benefit internally to PostgreSQL itself (excepting the minor pg_dump point above) so I can understand the reluctance to change anything. Reading the earlier discussion on this issue also suggests that this might be a really tricky change to make, I guess due to the way the parser is currently written, so cost/benefit is poor.

Anyway, for the record at least, we have a use case where it would be better for us if the original CREATE VIEW statement was returned as-is in the “information_schema.view_definition” column, but in compliance with Note13 any column expansion re-writes will have been applied. Over and above standards compliance I believe that the column expansion is structurally necessary anyway to facilitate subsequent table/column re-naming and the automatic cascade of the re-naming to views that use them. The further parser rewritten definition could continue to be stored in the pg_catalog in addition if it is needed internally within PostgreSQL (i.e. pg_dump). However, I also suggest that it might be sensible for the original “column expansion only” definition to be used in the pg_dump output, so that the “intent” of the view when originally created is what should (or should not in the case of any incompatibilities) migrate into a future version (or be restored into the same version).