Postgresql: create view and use of default tablespace?

Question

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.

Thanks for your response. No, just a standard select statement. But those were some of the 'under the hood' issues I thought might be at play. Also I wondered if caching to disk could be an issue for large datasets. Oracle caches the local heap to the users temporary tablespace. I wondered if that might be a reason as to why postgres required the owner to have permissions on the default tablespace; namely that it acted as a default temporary tablespace when the dataset exceeded a given heap size or parameter setting. But apparently not. — user108168, Feb 23 '22 at 21:23

score 4 · Accepted Answer · answered Feb 23 '22 at 07:24

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));
    }

Thanks for your reply. That answers my question perfectly. It's not a big issue for me I just wanted to make sure there wasn't an underlying design feature I wasn't aware of. — user108168, Feb 23 '22 at 13:47

Postgresql: create view and use of default tablespace?

1 Answers1