Postgresql: create view and use of default tablespace?-CodePudding

When I try to create a view in PostgreSQL 13 I get an error saying: "permission denied for tablespace tbs_dft". As you can see I've changed the system default tablespace. The problem is easy to fix by granting create on tablespace 'tbs_dft'. But my question is: why does it need to access the 'default tablespace' when creating a view containing a simple select statement? Although this is not a practical issue I am trying to learn Postgresql having come from Oracle and hence I'm not sure what it is I don't understand about the way View creation works in Postgresql.

Any information gratefully received.

CodePudding user response：

The reason is that this check is done whenever a relation is created (in DefineRelation in src/backend/commands/tablecmds.c). A relation is anything stored in pg_class: a table, an index, a sequence, a composite type, a view or a materialized view.

Now views or composite types do not have data files, so the check could be skipped in this case. If that is important for you, get in touch with development on the pgsql-hackers mailing list. This could be improved in my opinion.

Here is the code in question:

    /* Check permissions except when using database's default */
    if (OidIsValid(tablespaceId) && tablespaceId != MyDatabaseTableSpace)
    {
        AclResult   aclresult;

        aclresult = pg_tablespace_aclcheck(tablespaceId, GetUserId(),
                                           ACL_CREATE);
        if (aclresult != ACLCHECK_OK)
            aclcheck_error(aclresult, OBJECT_TABLESPACE,
                           get_tablespace_name(tablespaceId));
    }