I wrote an article a couple weeks ago regarding a shell script to run that would provide a postgres user read only access to every table in a specific database. Tonight I was updating a over a dozen databases and needed to provide read only access to all of them to a specific user so I decided to update the bash script from a couple of weeks ago to take a couple variables from the shell so you could specify database and user. The new script along with instructions on how to use it are below.
Bash Script To Provide Read Only Access To Every Table In A PostgreSQL Database:
- # Provide Read Only Access On PostgreSQL Database
- # Use: ./pgaccess $database $username
- tables=$(psql $1 -A -t -c "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';")
- for table in $tables
- echo "Providing select to $2 on $table"
- psql $1 -c "GRANT SELECT ON $table to $2;"
As you can see there are now two variables required to run the script which are database and username. The script above should be run in the below format. I name the script pgaccess.sh so that is what is used in the example below.
Read Only User Script Syntax:
- ./pgaccess $database $username
Just replace $database with the name of the database you are providing access to and replace $username with the name of the user that will be provided SELECT access to the PostgreSQL database.