PostgreSQL Discussion - newbie : setting access for users in a web enviroment

Author

newbie : setting access for users in a web enviroment

robert mena

2005-12-23, 8:23 pm

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.

Qingqing Zhou

2005-12-23, 8:23 pm

"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/.../sql-grant.html

Regards,
Qingqing

Peter Eisentraut

2005-12-23, 8:24 pm

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.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

robert mena

2005-12-24, 7:23 am

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.

On 12/23/05, Peter Eisentraut <peter_e@gmx.net> wrote:
> Am Freitag, 23. Dezember 2005 22:06 schrieb robert mena:
>
> 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.
>

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Bruno Wolff III

2005-12-25, 9:23 am

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.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links