Re:

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux