pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feed(no subject)
3+ messages / 3 participants
[nested] [flat]
* (no subject)
@ 2011-05-07 07:28 Didik Prasetyo <prasetyodidik62@yahoo.com>
0 siblings, 2 replies; 3+ messages in thread
From: Didik Prasetyo @ 2011-05-07 07:28 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re:
@ 2011-05-07 10:19 raghu ram <raghuchennuru@gmail.com>
parent: Didik Prasetyo <prasetyodidik62@yahoo.com>
1 sibling, 0 replies; 3+ messages in thread
From: raghu ram @ 2011-05-07 10:19 UTC (permalink / raw)
To: Didik Prasetyo <prasetyodidik62@yahoo.com>; +Cc: pgsql-admin
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@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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: (unknown)
@ 2011-05-07 11:00 Thomas Kellerer <spam_eater@gmx.net>
parent: Didik Prasetyo <prasetyodidik62@yahoo.com>
1 sibling, 0 replies; 3+ messages in thread
From: Thomas Kellerer @ 2011-05-07 11:00 UTC (permalink / raw)
To: pgsql-admin
Didik Prasetyo wrote on 07.05.2011 09:28:
> 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
You might consider upgrading, because since 9.x you can do:
GRANT SELECT ON ALL TABLES IN SCHEMA public;
Thomas
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-05-07 11:00 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-07 07:28 Didik Prasetyo <prasetyodidik62@yahoo.com>
2011-05-07 10:19 ` Re: raghu ram <raghuchennuru@gmail.com>
2011-05-07 11:00 ` Re: (unknown) Thomas Kellerer <spam_eater@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox