How To Remove Role Privileges Created By The PostgreSQL GRANT Command, Shell Script To Remove PostgreSQL Access Privileges

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.