Shell Script To Configure A Read Only User On A PostgreSQL Database

**UPDATE: New article here.

Its a little more complicated to create a read only database user with PostgreSQL than say using something like MySQL. You will need to grant select access on each table in the database for the read only user. Keep in mind that each time you add a new table you will need to either run the shell script below again or manually grant select access on the new table for the read only user.

Shell Script To Create A Read Only User On A PostgreSQL Database:

#!/bin/sh
#
# Provide Read Only Access On PostgreSQL Database
#

tables=$(psql database_name -A -t -c "SELECT table_name FROM information_schema.tables 
WHERE table_schema = 'public';")

for table in $tables
do
echo "Providing select to read_user on $table"
psql database_name -c "GRANT SELECT ON $table to read_user;"
done

You will need to run the above shell script as the user postgres from the local server or from any other user that has access to modify the proper database from the local server. Also make sure that the read-user actually exists so if the username is readonly make sure that this user exists before running the script. There are two variables that you will need to change in the script as well including database_name and read_user. Once the script is on the server and the variables have been replaced make sure it is executable by the postgres user by issuing the below two commands.

Change Script Ownership and Permissions:

chown postgres.postgres psqlreadonly.sh
chmod 755 psqlreadonly.sh

Make sure the script is saved in a directory such as /var/lib/pgsql that the postgres user has access to and then execute the script as shown below. Also below is a small example output of what will be returned when running the script.

Run PostgreSQL Read Only User Shell Script:

-bash-3.2$ ./psqlreadonly.sh
Granting select to read-only-user on atp_item_results
GRANT
Granting select to read-only-user on atp_items
GRANT
Granting select to read-only-user on atp_items_device_revisions
GRANT
Granting select to read-only-user on atp_test_results
GRANT
Granting select to read-only-user on atp_test_results_rmas
GRANT
Granting select to read-only-user on atp_test_results_work_orders
GRANT
Granting select to read-only-user on call_center_deferreds
GRANT

Simply change the variables for each database or each user that you want to provide read only access for and this script should save you a lot of time.

**UPDATE: New article here.


List Price: $36.99 USD
New From: $36.24 USD In Stock
Used from: $13.93 USD In Stock


List Price: $14.99 USD
New From: $4.97 USD In Stock
Used from: $0.01 USD In Stock

Share