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 <prasetyodidik62@xxxxxxxxx> 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@xxxxxxxxxxxxx'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
