You are here

Read-Only user access to DB

Subscribe to Syndicate
It is possible to create a user that allows read only queries via pgadmin. The user and password should be safeguarded and provided to only those that need to do reports directly on tables.

 

To do so, Artsman Support Staff will need to do the following steps on your database:

  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 select '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
  5. Open PGadmin again
  6. 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