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:

#!/bin/sh
#
# 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
do
echo "Providing select to $2 on $table"
psql $1 -c "GRANT SELECT ON $table to $2;"
done

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.


List Price: $36.99 USD
New From: $34.58 USD In Stock
Used from: $11.94 USD In Stock

PostgreSQL 8.4 Official Documentation - Volume IV. Reference (Paperback)

By (author): The PostgreSQL Global Development Group


List Price: $35.99 USD
New From: $35.99 USD In Stock
Used from: $6.95 USD In Stock

DeliciousStumbleUponDiggTwitterFacebookRedditLinkedInEmail
Tags: , , , , , , , , , , ,
8 Responses to “Updated Bash Script To Provide Read Only Postgres Database Table Access”
  1. Robert Treat says:

    Nice script. BTW, have you seen the new grant capabilities coming in 8.5?
    http://www.depesz.com/index.php/2009/11/07/waiting-for-8-5-grant-all/

    [Reply]

    alex Reply:

    Hello Robert Treat,

    Thanks. I hadn’t seen that yet. Finally will be much easier!

    Thanks.
    alex

    [Reply]

  2. Anoop says:

    Hi Robert,
    I started with your script and for some reason had issues, so I’ve edited it a bit to make it work as a bash script. Also the db I am working with has schemas other than public which I want to allow the user read access. The edited script will in addition create the user (and complain if the user already exists) and give usage access to the schemas in addition to the tables. Could probably combine those two together since I am pulling schema info also for granting table access. I ran the script as user postgres on version 8.3.

    Alex thanks for the note about grant-all in 8.5, nice!

    #!/bin/bash
    #
    # Provide Read Only Access On PostgreSQL Database
    #
    # Use: ./pgaccess $database $username $password
    #

    ## Create ReadOnly User
    echo "Creating User $2"
    #echo 'psql -U postgres -t -c "CREATE ROLE '$2' PASSWORD '$3' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;"'
    psql -U postgres -t -c "CREATE ROLE $2 PASSWORD '$3' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;"

    ## Grant access to user for schemas
    schemas=$(psql $1 -A -t -c "SELECT DISTINCT table_schema as table_info FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog' , 'information_schema');")

    for schema in $schemas
    do
    echo "Providing usage to $2 on $schema"
    #echo "psql $1 -A -t -c 'GRANT USAGE ON SCHEMA ${schema} to $2;'"
    psql $1 -A -t -c "GRANT USAGE ON SCHEMA ${schema} to $2;"
    done

    ## Grant select access to user for tables
    tables=$(psql $1 -A -t -c "SELECT table_schema,table_name as table_info FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog' , 'information_schema');")

    for table in $tables
    do
    schema_table=`echo ${table} | sed 's/|/./g'`
    echo "Providing select to $2 on $table"
    #echo "psql $1 -A -t -c 'GRANT SELECT ON ${schema_table} to $2;'"
    psql $1 -A -t -c "GRANT SELECT ON ${schema_table} to $2;"
    done

    [Reply]

    alex Reply:

    Hello Anoop,

    I think you got the names mixed up. :) Robert provided the info on the grant all capabilities and I provided the initial script. heh. Anyhow thanks for the additions to the script. Looks great if you need to add a new user and provide them read access at the same time. Typically when I am providing read access to a user they already exist so I am updating a user for some new tables that were added or something. I will try the above script next time I need to add a user with read-only status though.

    Thanks.
    alex

    [Reply]

  3. Anoop says:

    Here’s a newer version of the script I’m using, it has support for older versions Postgresql of since they use a different command to create users.

    #!/bin/bash
    #
    # Script to create ReadOnly User/Role
    # And provide Read Only Access On PostgreSQL Database
    # Based On: http://www.question-defense.com/2009/11/18
    # Use: /usr/local/bin/pgaccess_ro.sh $action $database $username $password
    #

    # ACCEPT COMMAND LINE VARIABLES
    DB=$2
    USER=$3
    PWD=$4
    #################

    ## Create ReadOnly Role/User
    create_ro_user() {
    echo "Creating ReadOnly Role/User $USER"
    psql $DB -U postgres -t -c "CREATE ROLE $USER PASSWORD '$PWD' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;"
    #echo 'psql -U postgres -t -c "CREATE ROLE '$USER' PASSWORD '$PWD' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;"'
    }

    # Older Postgresql servers use different command to create users.
    PG7-create_ro_user() {
    echo "Creating ReadOnly Role/User $USER - Older Postgresql Versions"
    psql $DB -U postgres -t -c "CREATE USER $USER PASSWORD '$PWD' NOCREATEDB NOCREATEUSER;"
    #echo 'psql -U postgres -t -c "CREATE USER '$USER' PASSWORD '$PWD' NOCREATEDB NOCREATEUSER;"'
    }

    ## Grant access to user for schemas
    grant_schema_access() {
    schemas=$(psql $DB -A -t -c "SELECT DISTINCT table_schema as table_info FROM information_schema.tables \
    WHERE table_schema NOT IN ('pg_catalog' , 'information_schema');")

    for schema in $schemas
    do
    echo "Providing usage to $USER on $schema"
    psql $DB -A -t -c "GRANT USAGE ON SCHEMA ${schema} to $USER;"
    #echo "psql $DB -A -t -c 'GRANT USAGE ON SCHEMA ${schema} to $USER;'"
    done
    }

    ## Grant select access to user for tables
    grant_table_select() {
    tables=$(psql $DB -A -t -c "SELECT table_schema,table_name as table_info FROM information_schema.tables \
    WHERE table_schema NOT IN ('pg_catalog' , 'information_schema');")

    for table in $tables
    do
    schema_table=`echo ${table} | sed 's/|/./g'`
    echo "Providing select to $USER on $table"
    psql $DB -A -t -c "GRANT SELECT ON ${schema_table} to $USER;"
    #echo "psql $DB -A -t -c 'GRANT SELECT ON ${schema_table} to $USER;'"
    done
    }

    case "$1" in
    create_ro_user)
    create_ro_user
    ;;

    PG7_create_ro_user)
    PG7-create_ro_user
    ;;

    setup_ro_access)
    grant_schema_access
    grant_table_select
    ;;

    create_ro_user_setup_access)
    create_ro_user
    grant_schema_access
    grant_table_select
    ;;

    PG7_create_ro_user_setup_access)
    PG7-create_ro_user
    grant_schema_access
    grant_table_select
    ;;
    *)
    echo " * Usage: ./pgaccess_ro.sh setup_ro_access {db name} {user name}"
    echo " ./pgaccess_ro.sh create_ro_user {db name} {user name} {password}"
    echo " ./pgaccess_ro.sh create_ro_user_setup_access {db name} {user name} {password}"
    echo " ./pgaccess_ro.sh PG7_create_ro_user_setup_access {db name} {user name} {password}"
    exit 1
    ;;
    esac;

    [Reply]

    alex Reply:

    Hello Anoop,

    We haven’t personally tested the script above but thanks for sharing. I do plan on testing it on multiple versions of PGSQL in the future. We do really appreciate you taking the time to post comments especially detailed like the above with an expanded bash script to add read only access to PostgreSQL.

    Thanks.
    alex

    [Reply]

  4. anonymous says:

    I modified the code slightly to be a bit more efficient:

    #!/bin/sh
    #
    # 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’;”)

    tables=(echo ${tables[@]} | sed ‘s- -, -g’;
    psql –quiet -c “GRANT SELECT ON $tables TO $2;” $1;
    # For a file-like command:
    # echo “GRANT SELECT ON $tables TO $2;” | psql –quiet -f – $1;

    [Reply]

    alex Reply:

    Hello anonymous,

    Awesome! I am by no means a pro when it comes to scripts so any improvements are always welcome! Thanks for taking the time to post the improvements.

    Thanks.
    alex

    [Reply]

  5.  
Leave a Reply

*Type the letter/number combination in the abvoe field before clicking submit.

*