newbie : setting access for users in a web enviroment
Hi,
I am new to postgres but coming from a MySQL enviroment.
I am confused with the necessary steps to create users and restrict them to
access/delete/insert/update data and create/delete/alter tables in a
specific database.
I've created a database test and a user testadm
createdb test
createuser -D -P testadm
Enter password for new user:
Enter it again:
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER
psql test
\du
List of users
User name | User ID | Attributes | Groups
----------------+---------+----------------------------+--------
testadm | 100 | |
postgres | 1 | superuser, create database |
GRANT CREATE,REFERENCES ON DATABASE test TO testadm;
\z
Access privileges for database "test"
Schema | Name | Type | Access privileges
--------+------+------+-------------------
How can I specify that the user testadm can perform those actions to this
database?
Tks.
"robert mena" <robert.mena@gmail.com> wrote
How can I specify that the user testadm can perform those actions to this
database?
Use GRANT command (the opposite is REVOKE). I suppose you are using 8.1:
http://www.postgresql.org/docs/8.1/static/sql-grant.html
Regards,
Qingqing
Am Freitag, 23. Dezember 2005 22:06 schrieb robert mena:
GRANT CREATE,REFERENCES ON DATABASE test TO testadm;
\z
Access privileges for database "test"
Schema | Name | Type | Access privileges
--------+------+------+-------------------How can I specify that the user testadm can perform those actions to this
database?
For one thing, the command \z shows table privileges, so the empty table above
is not surprising. pg_database would give you better information.
Second, the privilege type REFERENCES does not exist for databases, only for
tables, so the command you executed does not make sense.
I suggest you peruse the GRANT manual page again.
Hi,
thanks for the reply.
I've already read the docs. I am using 8.0.5 btw.
One of the things I am confused is how can I give the privileges to
the database without having to know the specific tables.
The grant command when applied to a database simply mentions CREATE so
the user can create tables. But when applied to tables, where I can
specify specific privileges I need to know the table...
If I'd need to define a user with SELECT privileges to all tables in
my test database, how could I do that?
A simple example would be fine.
Show quoted text
On 12/23/05, Peter Eisentraut <peter_e@gmx.net> wrote:
Am Freitag, 23. Dezember 2005 22:06 schrieb robert mena:
GRANT CREATE,REFERENCES ON DATABASE test TO testadm;
\z
Access privileges for database "test"
Schema | Name | Type | Access privileges
--------+------+------+-------------------How can I specify that the user testadm can perform those actions to this
database?For one thing, the command \z shows table privileges, so the empty table
above
is not surprising. pg_database would give you better information.Second, the privilege type REFERENCES does not exist for databases, only for
tables, so the command you executed does not make sense.I suggest you peruse the GRANT manual page again.
On Sat, Dec 24, 2005 at 08:41:49 -0400,
robert mena <robert.mena@gmail.com> wrote:
If I'd need to define a user with SELECT privileges to all tables in
my test database, how could I do that?
You can't. You can write a script that will give them access to all of the
tables that currently exist. But if you create new ones, they won't get any
access by default to the new tables.