Thread: 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.

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.