PostgreSQL isn’t the most friendly when it comes to creating access to various databases or tables within those databases however with a couple easy shell scripts it becomes much easier. I previously wrote this article regarding a shell script created to provide SELECT access, which is essentially read only access, to every table located within a specified database. The problem now might be if you want to remove a user from having access to that database you will be required to manually remove each access privilege for the role you created. Below is the reverse of the initial shell script which will remove SELECT access from each table within a specified database.
Shell Script That Removes SELECT Access From Every Table In A PostgreSQL Database:
- #!/bin/sh
- #
- # Remove 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
- do
- echo "Removing SELECT to $2 on $table"
- psql $1 -c "REVOKE SELECT ON $table FROM $2;"
- done
As you can see above the shell script is fairly simple. It first looks at the PostgreSQL database specified and puts every table into an array. The second portion of the script goes through each table in the array and revokes SELECT access to a specified user. The $1 is the first variable input on the command line after the command and the $2 is the second variable input on the command line after the command. Below is an example of the script I named removepgaccess.sh.
The removepgaccess.sh Command In Action:
- -bash-3.1$ ./removepgaccess.sh somedatabase someuser
- Removing select to someuser on sometable1
- REVOKE
- Removing select to someuser on sometable2
- REVOKE
- Removing select to someuser on sometable3
- REVOKE
- Removing select to someuser on sometable4
- REVOKE
- Removing select to someuser on sometable5
- REVOKE
- Removing select to someuser on sometable6
- REVOKE
- Removing select to someuser on sometable7
- REVOKE
- Removing select to someuser on sometable8
- REVOKE
- Removing select to someuser on sometable9
- REVOKE
- Removing select to someuser on sometable0
- REVOKE
- -bash-3.1$
The SELECT access for the specified database and user has now been removed. You will need to issue the above command for each database on the PostgreSQL server that the specified role has SELECT access too.
