Play SQL Spreadsheets - Permissions wrt Confluence.

Hi Rashmi and Julian,

Sorry for the delay, I was travelling last week and I couldn't gather enough time to understand what happened with your schemas.

For Rashmi:

Here are two changes you can make to correct your {{ALTER TABLE testbpr SET SCHEMA space_pst; }} query:

• Use double-quotes around names and ensure you are using the correct case. Postgres is case-sensitive, but when you write any entity without quotes it assumes the names are lower-case, which is disturbing. Postgres always displays names with the correct case.

• Make sure the "testbpr" table is visible in the current schema. For example:

  • ALTER TABLE testbpr SET SCHEMA playsqltest executed properly because the table was in the public schema and was moved to playsqltest.

  • but ALTER TABLE testbpr SET SCHEMA space_pst doesn't execute properly because the table is now in the schema playsqltest and it's not the default schema.

Hence the result:

For Julian:

The "Tables" icon is visible on a per-space basis. If you don't want unintended users to use the product:

• You can remove the icon from the space sidebar: Click Space Tools at the bottom of the sidebar, select Configure then click on the - (minus) icon next to the link, so that it is not visible.

• If you only want the add-on to be visible in a few selected spaces, then you can go the the Play SQL administration and remove the global connection to the database. Then in each space where you want a connection, click the Space Tools button at the bottom of the sidebar, select Play SQL, and add a connection. This way, the icon will only appear on space where a connection is configured. You can put exactly the same connection detail in each space, knowing that $spaceKey will be replaced by the correct value.

I will be much more available this week for support.

Best regards,
Adrien

Hi Adrien

In addition to Rashmi's question from yesterday I have another question.

Play SQL was installed to address a very specific requirement for a very small number of users. It was never intended that it shoud be widely used and available to Confluence users in general.

Unfortunately what appears to have happened is that once we installed PlaySQL the 'Tables' icon to give access to PlaySQL becomes available and visible to all users. A number of users have then clicked on this - presumably just to see what it does - and created entities in PlaySQL. This was never the intention and use of PlaySQL in our Confluence instance is not controlled in the way we would like.

Basically we have a requirement to disable or hide the 'tables' icon / PlaySQL for users we do not want to make use of it. Ideally we would like this to be based on group roles / permissions but would consider any solution to this that you can suggest. We could presumably edit the HTML header to conditionally display / hide references to PlaySQL based on user role but we wondered if there is a better way of doing what we need?

Thanks

Julian

Hello Adrien,

We are facing problem with moving the tables / spreadsheets from New Schema to the space specific schema. Have documented the steps we followed and the issue we are facing with restoring the tables from new schema to space specific schema.

Please have a look and let us know how can we proceed further with restoring the old tables which were present before the Schema changes.

Thank you,

Open

Regards,
Rashmi

Hi Rashmi,

If I understand, you have two problems:

1. When you move a table using the ALTER TABLE command, it appears in the new space but you meet an error when you try to display them.

2. You don't know in which space the spreadsheets should go.

Concerning 1, did you have uppercases in tables names or in the schema name? When exactly do you see the message "relationship table_name doesn't exist"?

Concerning 2, I don't think there is any way to know who created which spreadsheet. Did you have the Audit Trail enabled? If so, then the information is written in the playsql_audit_trail table. If not, then it may be time to activate the Audit Trail in each space: Go to the Space Tools (at the bottom of the sidebar), choose Play SQL, then open the tab "Audit Trail". You can select "Keep history" and set a deletion period. There is 1 audit trail per space.

Regards,
Adrien

Hello Adrien,

To add-on, We have Playsql Spreadsheets created in our confluence. But we are not able to find out who has created those spreadsheets or to which space those spreadsheets belongs as Playsql DB was configured to store the spreadsheets in the default schema not with the space specific schema.

Could you please let us know if there is any meta data or attributes anywhere we can query to find out who created a those playsql spreadsheets / tables or which space it is part of.

Thank you,

Regards,
Rashmi.