10.1 User Administration and User Rights


Like all other sophisticated databases, PostgreSQL allows the administrator to define users as well as user rights. This is important for many reasons: On the one hand, it helps you to protect your data from unauthorized access. On the other hand, it helps you to protect yourself. Just imagine various different tables user rights can help you make sure that you do not accidentally delete important data from one of the tables.

10.1.1 Managing Users and Groups

To create new users, use the CREATE USER command. The next listing shows the syntax overview of the command:

 phpbook=# \ h CREATE USER Command:     CREATE USER Description: define a new database user account Syntax: CREATE USER username [ [ WITH ] option [ ... ] ] where option can be:           SYSID uid         | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'         | CREATEDB | NOCREATEDB         | CREATEUSER | NOCREATEUSER         | IN GROUP groupname [, ...]         | VALID UNTIL 'abstime' 

The CREATE USER command provides many settings and options to configure a user to your needs. With the help of SYSID, you can assign a user to a specific id. This can be useful in case of changes and modifications after adding or removing existing users. In the next step a password has to be provided. The password can be stored in either an encrypted or an unencrypted way.

A user can be allowed to create new users as well as databases. We strongly recommend not allowing new users to do that unless you like security holes in your user system.

Just as on Unix, a user can be assigned to a list of groups. In general, PostgreSQL's way of handling users and groups is very similar to the one used by Unix.

Let's create a new user called hs:

 phpbook=# CREATE USER hs ENCRYPTED PASSWORD 'mypasswd' NOCREATEDB NOCREATEUSER; CREATE USER 

If no error occurred, the user has been created successfully and you can connect to the database as hs now. The information about the user is stored in so-called system tables. System tables are hidden tables that store internal information about various aspects, such as users, functions, operators, or optimizer statistics. The system table called pg_shadow contains all information about the user:

 phpbook=# SELECT usename, usesysid, usecreatedb, usetrace, usesuper, usecatupd   FROM pg_shadow;  usename  | usesysid | usecreatedb | usetrace | usesuper | usecatupd ----------+----------+-------------+----------+----------+-----------  postgres |        1 | t           | t        | t        | t  hs       |      100 | f           | f        | f        | f (2 rows) 

As you can see in the third column, hs is not allowed to create new databases and in the fifth column, hs is not a superuser. In this scenario, the password is stored in an encrypted way:

 phpbook=# SELECT usename, passwd  FROM pg_shadow;  usename  |               passwd ----------+-------------------------------------  postgres |  hs       | md51eafef644f38fe6ce05fbb985bd72fdc (2 rows) 

The default superuser called postgres does not have a password we recommend that you change this when setting up PostgreSQL.

To remove a user from the database, you can use DROP USER. DROP USER removes the user from the system table:

 phpbook=# \ h DROP USER Command:     DROP USER Description: remove a database user account Syntax: DROP USER name 

Simply pass the name of the user you want to delete to the function.

Just as on Unix machines, a user can be assigned to a group. Groups can be used to build packages of user rights that can be assigned to more than just one user at once. This will save a lot of administration overhead and help to avoid errors in your user configuration. Let's take a look at the syntax overview of the CREATE GROUP command.

 phpbook=# \ h CREATE GROUP Command:     CREATE GROUP Description: define a new user group Syntax: CREATE GROUP name [ [ WITH ] option [ ... ] ] where option can be:      SYSID gid    | USER  username [, ...] 

The command can be used easily:

 phpbook=# CREATE GROUP cybertec USER hs, postgres; CREATE GROUP 

In this example two users have been added to the group called cybertec.

To remove a group, the DROP GROUP command is provided:

 phpbook=# \ h DROP GROUP Command:     DROP GROUP Description: remove a user group Syntax: DROP GROUP name 

After groups and users have been created, you might want to change the configuration of a user or a group. Therefore the ALTER command is provided. In SQL you can use the ALTER command to modify almost all objects in a database. Tables, users, groups all kinds of objects can be modified by using just one single command. To modify users, ALTER USER is the command you need:

 phpbook=# \ h ALTER USER Command:     ALTER USER Description: change a database user account Syntax: ALTER USER username [ [ WITH ] option [ ... ] ] where option can be:           [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'         | CREATEDB | NOCREATEDB         | CREATEUSER | NOCREATEUSER         | VALID UNTIL 'abstime' 

All parameters you have defined when creating the user can be changed using ALTER. Let's look at an example:

 phpbook=# ALTER USER hs VALID UNTIL '2004/12/31'; ALTER USER 

In this example you can see that hs is only allowed to access the database until December 31, 2004. The information has been added to the system table:

 phpbook=# SELECT usename, valuntil  FROM pg_shadow;  usename  |        valuntil ----------+------------------------  postgres |  hs       | 2004-12-31 00:00:00+01 (2 rows) 

ALTER GROUP is a command used for modifying groups. With the help of this command, it is possible to add or remove users from a group. Take a look at the syntax of the command:

 phpbook=# \ h ALTER GROUP Command:     ALTER GROUP Description: add users to a group or remove users from a group Syntax: ALTER GROUP name ADD USER username [, ... ] ALTER GROUP name DROP USER username [, ... ] 

The syntax is easy you only have to define the group and the user you want to add or remove from a group. In the next example, the user postgres is removed from the group called cybertec. First, you can take a look at the content of pg_group:

 phpbook=# SELECT * FROM pg_group;  groname  | grosysid | grolist ----------+----------+---------  cybertec |      100 | {100,1} (1 row) 

As you can see in the third column, two users are in the group. The list of users is represented by an array and contains the ids of the users. In the next step, the ALTER command can be used:

 phpbook=# ALTER GROUP cybertec DROP USER postgres; ALTER GROUP 

After executing the ALTER command, there is only one user left in the group:

 phpbook=# SELECT * FROM pg_group;  groname  | grosysid | grolist ----------+----------+---------  cybertec |      100 | {100} (1 row) 

Creating users and groups is an easy task because PostgreSQL has a simple and easy-to-understand model for managing users.

10.1.2 Managing User Rights

After users have been created, it is time to define the rights of these users. Like almost all other databases, PostgreSQL provides two commands that are essential for managing user rights: GRANT and REVOKE.

10.1.2.1 GRANT

GRANT is used to give a user certain rights. A set of permissions can be granted to a user: SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES, and TRIGGER.

Rights are usually granted on objects in the database such as tables and sequences. A right can be assigned to a user, a group, or to the public.

Let's look at the syntax of GRANT:

 phpbook=# \ h GRANT Command:     GRANT Description: define access privileges Syntax: GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }  [,...] | ALL [ PRIVILEGES ] }     ON [ TABLE ] objectname [, ...]     TO { username | GROUP groupname | PUBLIC } [, ...] 

As you can see, the syntax of the command is easy. After this theoretical overview, it is time to take a look at a practical example.

The first thing to do is to create a new table. This time a table for storing products will be created:

 phpbook=# CREATE TABLE product (id int4, name text, price numeric(9,2)); CREATE 

The table has been created as user postgres, which is the default user. With the help of the \c command, it is possible to switch to user hs:

 phpbook=# \ c phpbook hs You are now connected to database phpbook as user hs. 

Because hs is not the owner of the table, he is not allowed to select data from the table:

 phpbook=> SELECT * FROM product; ERROR:  product: Permission denied. 

To solve the problem, the owner of the table has to grant some rights to hs. Therefore you can switch to user postgres:

 phpbook=> \ c phpbook postgres You are now connected to database phpbook as user postgres. 

To give user hs some rights, you can use the GRANT command:

 phpbook=# GRANT SELECT, INSERT, UPDATE, DELETE ON product TO hs; GRANT 

With the help of the \z command, you can find out which rights have been defined for which user:

 phpbook=# \ z product    Access privileges for database "phpbook"   Table  |         Access privileges ---------+------------------------------------  product | {"=","postgres=arwdRxt","hs=arwd"} (1 row) 

In this scenario postgres is allowed to do everything and hs is allowed to SELECT, INSERT, UPDATE, and DELETE records. Now that hs has enough rights to select data from the table, you can switch back to user hs and select all data from the table:

 phpbook=# \ c phpbook hs You are now connected to database phpbook as user hs. phpbook=> SELECT * FROM product;  id | name | price ----+------+------- (0 rows) 

As you can see, hs is allowed to retrieve data now. postgres has given hs a set of rules but hs wants more:

 phpbook=> GRANT ALL ON product TO hs; ERROR:  permission denied 

The error occurring seems logical because hs is not allowed to grant rights to himself.

10.1.2.2 REVOKE

REVOKE is the counterpart of GRANT and can be used to take rights from a user. The syntax of the command is almost identical to that of GRANT:

 phpbook=# \ h REVOKE Command:     REVOKE Description: remove access privileges Syntax: REVOKE { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }  [,...] | ALL [ PRIVILEGES ] }     ON [ TABLE ] object [, ...]     FROM { username | GROUP groupname | PUBLIC } [, ...] 

In the previous section (GRANT), you have seen that a user is not allowed to grant himself rights. The next example shows that a user is not allowed to take rights away from himself:

 phpbook=> REVOKE ALL ON product FROM hs; ERROR:  permission denied 

To take some rights from hs, you have to switch to user postgres:

 phpbook=> \ c phpbook postgres You are now connected to database phpbook as user postgres. 

Because postgres is the superuser, he is allowed to modify all rights:

 phpbook=# REVOKE ALL ON product FROM hs; REVOKE 

If you have a closer look at the overview of rights defined on product, you can see that hs is not mentioned and he no longer has rights:

 phpbook=# \ z product Access privileges for database "phpbook"   Table  |    Access privileges ---------+--------------------------  product | {"=","postgres=arwdRxt"} (1 row) 

After changing the rights, you can switch back to hs:

 phpbook=# \ c phpbook hs You are now connected to database phpbook as user hs. 

Because all rights have been revoked from hs, he is no longer allowed to query the table:

 phpbook=> SELECT * FROM product; ERROR:  product: Permission denied. 
10.1.2.3 About Superusers

When working with superusers, some basic points must be taken into consideration. As you have already seen, the default superuser is called postgres. This user has all rights and is allowed to create new users as well as new databases. There is no operation that cannot be performed by the superuser. The difference between a new superuser and a new user is that it is not possible to revoke rights from a superuser using REVOKE. This is an important point when working with PostgreSQL.

To create a new user in our scenario, you have to be a superuser:

 phpbook=# \ c phpbook postgres You are now connected to database phpbook as user postgres. 

A new superuser can be created by using CREATE USER in combination with the flag CREATEUSER. If a user is allowed to create new users, he is a superuser. Take a look at an example:

 phpbook=# CREATE USER superhs ENCRYPTED PASSWORD 'mypasswd' CREATEUSER; CREATE USER 

In the next step a table is created:

 phpbook=# CREATE TABLE pet (name text, birthday date); CREATE 

To connect as the new user, you can use \c again:

 phpbook=# \ c phpbook superhs You are now connected to database phpbook as user superhs. 

Because superhs is superuser, he is allowed to query the table even if postgres has not granted rights to him:

 phpbook=# SELECT * FROM pet;  name | birthday ------+---------- (0 rows) 

If you switch back to postgres and ask PostgreSQL which rights have been defined on the table, you will receive an empty list:

 phpbook=# \ c phpbook postgres You are now connected to database phpbook as user postgres. phpbook=# \ z pet Access privileges for database "phpbook"  Table | Access privileges -------+-------------------  pet   | (1 row) 

If rights are revoked from the superuser, PostgreSQL will list these changes in the list of access privileges:

 phpbook=# REVOKE ALL ON pet FROM superhs; REVOKE phpbook=# \ z pet Access privileges for database "phpbook"  Table |    Access privileges -------+--------------------------  pet   | {"=","postgres=arwdRxt"} (1 row) 

Even if superhs has no rights, he is able to query the table:

 phpbook=# \ c phpbook superhs You are now connected to database phpbook as user superhs. phpbook=# SELECT * FROM pet;  name | birthday ------+---------- (0 rows) 

Why does PostgreSQL change the list of rights if it doesn't affect the behavior of the user? Well, the answer is simple. In pg_shadow you can see if a user is a superuser or not. If you revoke rights from a user and update the usesuper column, the user will have to face all restrictions:

 phpbook=# SELECT usename, usesuper FROM pg_shadow;  usename  | usesuper ----------+----------  postgres | t  hs       | f  superhs  | t (3 rows) 


PHP and PostgreSQL. Advanced Web Programming2002
PHP and PostgreSQL. Advanced Web Programming2002
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 201

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net