Read-Only user access to DB

You can create a user that allows read only queries on the Theatre Manager database via:

• pgadmin,
• another external tool that can access a postgres database directly,
• using the REST API

The user and password should be safeguarded and provided to only those that need to do reports directly on tables.

Generally, the REST API should be used to provide far more controlled access to data than using the carte-blanche SQL access as described above. The REST api: applies restrictions to data that a person can normally see consistent with their login privileges provides a variety of output formats like JSON, HTML, Excel, etc - and- can be access from any location with a web browser, rather than restricted to internal use may allow restricted update access to keep the data consistent. Direct SQL access can only to be provided under limited circumstances and should only be used by experienced people.

 

Setting up Access

Artsman Support Staff will need access to your database to do the following steps:

1. Access PGadmin using the standard manner
2. Create a user account by
   • Scroll down to the Login Roles and right clicking to create a new login role
   • under properties, give the role a name like ReportUser
   • under definition tab, set a password
   • under role priveledges, only pick:
     • can login and
     • inherit rights from parent roles.
     Make sure all others are not checked.
   • save and close the role creation window
3. Open up an SQL window for the specific database
   • Type the command below and replace 'ReportUser' with the name of the role you created above
   • GRANT SELECT ON ALL TABLES IN SCHEMA public TO "ReportUser"
   • run the SQL
4. Close and QUIT PGadmin

 

Testing Access via Pgadmin

1. Open PGadmin again
2. Make a new connection
   • Give it a name like 'report access'
   • use the database IP address
   • Use 'ReportUser' as the name (or whatever you called it
   • enter the password you used to create the role
   • DO NOT SAVE THE PASSWORD
   • Try to connect
3. Try a SQL command like the following to see that data is read:

   Select * from F_EMPLOYEES