We can not Grant SELECT privileges to entire Database. Granting access to a
schema allows use of that schema. It does not grant access to objects
contained in the schema. You will need to do a grant for each object.
*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.
--Raghu Ram
On Sat, May 7, 2011 at 12:58 PM, Didik Prasetyo
<prasetyodidi...@yahoo.com>wrote:
> I want to ask how to make GRANT, where users can only perform SELECT on all
> the TABLE, in the database, which I have done the following
>
>
> login as: root
> root@203.142.85.52's password:
> Last login: Fri May 6 11:32:08 2011 from 10.10.5.22
> cd /us [root@dev ~]# cd /usr/local/pgsql/bin/
> [root@dev bin]# ./psql -U postgres
> psql (8.4.1)
> Type "help" for help.
>
> postgres=# CREATE USER udin WITH PASSWORD 'udin';
> CREATE ROLE
> postgres=# \du
> List of roles
> Role name | Attributes | Member of
> -----------+-------------+-----------
> dba | | {}
> postgres | Superuser | {}
> : Create role
> : Create DB
> udin | | {}
>
> postgres=# \q
>
> [root@dev ~]# cd /usr/local/pgsql/bin/
> [root@dev bin]# ./psql -U postgres
> psql (8.4.1)
> Type "help" for help.
>
> postgres=# GRANT SELECT ON DATABASE dbmufins to udin;
> ERROR: invalid privilege type SELECT for database
>
> but still there is error, I beg of solutions, from friends all
>
> thank you for your help
>
