Thursday, April 7, 2011

Postgresql read only user

0 comments

1. Create a new user:

CREATE USER postgresreadonly WITH PASSWORD 'abc';

2. Script to grant read only permission on databases objects:

#!/bin/bash

DBNAME=postgres
DBHOST=200.0.0.1
DBPORT=5432
PGUSER=postgres
export PGUSER
PGPASSWORD=xxxxxx
export PGPASSWORD

if [ -z $1 ]; then
  echo "!!!"
  echo "User name cannot be blank."
  echo "Example: grant_readonly_user.sh "
  echo "!!!"
  exit 1
fi

USERNAME=$1
TARGETDB=$2

valid_user_name=`psql -d $DBNAME -h $DBHOST -p $DBPORT  -c "SELECT  1 FROM pg_user where usename='"$USERNAME"'" -t -A -q`
if [ -z $valid_user_name]; then
  echo "FATAL: $USERNAME is not a valid user"
  exit 1
fi

if [ -z $2 ];
then
  DBNAME=postgres
  psql -d $DBNAME -h $DBHOST -p $DBPORT  -c "select datname from pg_database  where datistemplate=false and datallowconn=true" -t -A > /tmp/readonlyuser_dblist
  ret=$?
else
  DBNAME=$2
  psql -d $DBNAME -h $DBHOST -p $DBPORT  -c "select datname from pg_database  where datistemplate=false and datallowconn=true and datname='"$TARGETDB"'" -t -A > /tmp/readonlyuser_dblist
  ret=$?
fi

if [ $ret -gt  0 ];
then
 exit 1
fi
#
for dbname in `cat /tmp/readonlyuser_dblist`
do

echo $dbname
#This part is used to grant select permission on existing objects
psql -d $dbname -h $DBHOST -p $DBPORT  -c "SELECT 'GRANT SELECT ON '||n.nspname||'.'||c.relname || ' TO $USERNAME;' as c FROM pg_catalog.pg_class c       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r','v') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema'  AND n.nspname !~ '^pg_toast' AND pg_catalog.pg_table_is_visible(c.oid) " -t -A > /tmp/postgresreadonlyuser.sql
psql -d $dbname -h $DBHOST -p $DBPORT -f /tmp/postgresreadonlyuser.sql

#This part is used to set the privileges that will be applied to objects created in the future.
psql -d $dbname -h $DBHOST -p $DBPORT  -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO $USERNAME";
rm -f /tmp/postgresreadonlyuser.sql

done

rm -f /tmp/readonlyuser_dblist

exit 0

It takes two parameters. One is USERNAME and the other is DATABASE name where USERNAME is a mandatory parameter. This is is the target user name that needs the read only permission. If the second parameter (DATABASE name) is blank, then the script grant read only permission to all databases.

3. Execute script and grant read only permission on dbname to postgresreadonly:

/var/lib/pgsql/dbscripts/grant_readonly_permission_to_user.sh  postgresreadonly dbname

4. Execute script and grant read only permission on all databases to postgresreadonly:

/var/lib/pgsql/dbscripts/grant_readonly_permission_to_user.sh  postgresreadonly

5. Set pg_hba.conf
...

host     all    postgresreadonly             0.0.0.0/0         md5

...

Leave a Reply

Labels