PGSQL demands table ownership for result and resulttype tables

Technical
1

Hello,

I ran into an issue with the Postgres DB plugin.

  • PostgresException: 42501: must be owner of table result
  • PostgresException: 42501: must be owner of table resulttype

The Postgres DB is used to be able to store results from multiple benches into a single DB.
In order to allow different operators to execute, there is a DB user with limited permission - that user should not be granted permission to delete or drop the tables.
However, at this moment, the user needs to have full permission to avoid above error!

My understanding is

  • the DB user needs to be OWNER to be able the ALTER the table (which in Postgres only is granted to the owner)
  • my assumption is, the ALTER TABLE is needed to extend the result and resulttypes table with additional dimensions depending on the number of result columns to be stored. In case additional columns are needed during execution the table is modified. Though useful, this prohibits restricting the permissions to the DB.

Workaround 1: “GRANT postgres TO lab_operator;” - which grants owner permission to the lab_operator

Workaround 2: The owner (=postgres) initializes the tables result and resulttypes with sufficient columns …

Any views or recommendations! Thanks.

Best regards,

Gernot

2

Hi @gernot.hueber,

This is one of the problems with client applications connecting directly to a SQL server rather than through some API layer which can add a layer of security.

Personally, I don’t see a big problem with making lab_operator an owner of the result/resulttype tables. After all, even with limited permissions, they can still freely delete any rows. So you are already assuming none of the clients are malicious. So I suggest you go with workaround 1.

For workaround 2. We could do this, but I am a bit concerned that it might affect performance negatively if we add too many. How many columns are sufficient?

Workaround 3: Maybe you could create a special user, which has the permissions and you run the test plan once with that user. Then everybody else can run with the lab_operator user and not get into trouble because those columns already exist.

3

Hi Rolf,

Thanks for your reply and thoughts.

Regarding permissions for workaround 1: ideally, the lab_operator would not have permissions only to insert rather to change and delete. Or is that needed somewhere?

To have a intermediate layer with more permissions sounds reasonable. However, that layer would have full permissions again - so in the end, the client has a layer allowing full access.

Just as an idea: would it be possible to have a (modified) DB schema uses following:
Results:

  • ResultID
  • ResultSeriesID
  • DimID
  • DimValue

ResultsType:

  • ResultsTypeID
  • DimID
  • Dim
  • DimType

Current postfix of Dim0, Dim1, … would go into the field DimID. Yet, a single row in the Results table would be spread into multiple rows.

Best regards,

Gernot