Play SQL Spreadsheets - Permissions wrt Confluence.

Description

Hello Team,

We are using the Play SQL Spreadsheet version 2.11.4 in the Confluence Version - 5.8.14. We have noticited that - Play SQL Spreadsheets which are created in the Space can be accessed by the other users who doesn't have permissions for the Space. Users can view and access the Spreadsheets created irrespective of the Space and Permissions.

Consider a Scenario like - I have a Confluence Space "ABC" which has restricted access only my team can access the space. Also i have created a Play SQL Spreadsheet "xyz" for my space and set the permissions for the Spreadsheet like to access the Spreadsheet - Users must either have space permissions or belong to Accepted Groups which i have added.
But we have noticied that spreadsheet can be accessed by any user who doesn't have space permission and even the user doesnot belong to the group which i have mentioned while giving the permission to access the spreadsheets.

Could you please let us know is there any way to restrict access to the Spreadsheets with respect to the space or user group it is created by.

We want to know How can we restrict the access to Spreadsheets which are created for a particular confluence space so that the users of any other space cannot access the spreadsheets until unless they have permission access.

Please share your inputs on this.

Thank you,

Environment

Confluence Version - 5.8.14
Play SQL Spreadsheets Version - 2.11.4

Observations

None

Attachments

3
  • 03 Aug 2016, 11:35 AM
  • 14 Jul 2016, 11:40 AM
  • 12 Jul 2016, 02:24 PM

Requirement Yogi

Activity

Show:

Adrien Ragot (Old account)August 8, 2016 at 10:32 AM

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:

ALTER TABLE "playsqltest"."testbpr" SET SCHEMA "space_pst";

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

Julian FlattAugust 4, 2016 at 2:22 PM
Edited

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

RashmiAugust 3, 2016 at 11:35 AM

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,

Regards,
Rashmi

Adrien Ragot (Old account)July 29, 2016 at 2:36 PM

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

RashmiJuly 28, 2016 at 9:45 AM

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.

Answered / Support Question

Details

Assignee

Reporter

Labels

Requirement Yogi

Created June 29, 2016 at 1:04 PM
Updated October 3, 2019 at 11:10 AM
Resolved June 20, 2019 at 7:24 AM