Adding Users and Managing User Rights

Adding Users and Managing User Rights

Managing user rights in PostgreSQL is an important thing. However, PostgreSQL's system for managing user rights is not that sophisticated as yet. In this section, you will learn the basics of PostgreSQL's way of managing users.

A General Overview

One of the primary tasks of a database administrator is user management. Managing users is an extremely important issue, not only in multiuser environments. Restrictions for certain users do not only protect your database from being accessed by someone who is not allowed to modify the data, it also helps you to protect yourself from accidental operations.

PostgreSQL offers a Unix-like system for managing users. Users can be assigned to groups and therefore have certain rights for modifying certain tables. All user information, as you might expect, is stored in tables. This is done by almost all advanced databases.

The supervisor user is called postgres . The postgres user has all rights and is allowed to perform all modifications.

Adding and Deleting Users

Let's come to some practical stuff and create some users. PostgreSQL offers two methods for creating new users. One is to use a simple SQL command, and the other one is to use a simple program called createuser . Let's take a look at the createuser program first because this will give you a good overview of the options we have when creating users. To find out more about createuser use

createuser --help

in your favorite Unix shell.

createuser creates a new PostgreSQL user.

Usage:
  createuser [options] [username]
Options:
  -d, --createdb                  User can create new databases
  -D, --no-createdb               User cannot create databases
  -a, --adduser                   User can add new users
  -A, --no-adduser                User cannot add new users
  -i, --sysid=SYSID               Select sysid for new user
  -P, --pwprompt                  Assign a password to new user
  -h, --host=HOSTNAME             Database server host
  -p, --port=PORT                 Database server port
  -U, --username=USERNAME         Username to connect as (not the one to create)
  -W, --password                  Prompt for password to connect
  -e, --echo                      Show the query being sent to the backend
  -q, --quiet                     Don't write any messages

Report bugs to <pgsql-bugs@postgresql.org>.

You can see that there are quite a few options when creating a new user. In the following example, we create a user called epi who will not be allowed to create new databases but will be allowed to create new users. The user will have the password anypasswd . To show you what happens inside the database, we have enabled the -e (echo) flag:

bash-2.04$

createuser -D -a -P -e epi

Enter password for user "epi":
Enter it again:
CREATE USER "epi" WITH  PASSWORD 'anypasswd' NOCREATEDB CREATEUSER
CREATE USER

PostgreSQL performs a simple SQL command to create the new user. We could have used the same SQL command with psql to achieve the result.

The CREATE USER command has a variety of options:

persons=#

\h CREATE USER

Command:     CREATE USER
Description: Creates a new database user
Syntax:
CREATE USER username
    [ WITH
     [ SYSID uid ]
     [ PASSWORD 'password' ] ]
    [ CREATEDB    NOCREATEDB ] [ CREATEUSER  NOCREATEUSER ]
    [ IN GROUP     groupname [, ...] ]
    [ VALID UNTIL  'abstime' ]

You may have noticed that the flags of the createuser program are very similar to the options of the CREATE USER command because createuser produces SQL. That is not surprising.

The following is another example of a CREATE USER command:

CREATE USER hans WITH PASSWORD 'anypasswd';

Now we want to connect to the database as user hans . We have to use an additional flag when starting psql . -U defines the user we want to use to connect to the database:

psql -U hans shop

If the user has successfully been created, the command above should work. We can now work with the database and do all modifications we are allowed to do.

As we have mentioned before, user information is stored in tables. In the case of PostgreSQL, we can use the view called pg_user to check for user information:

persons=#

\d pg_user

View "pg_user"
  Attribute    Type    Modifier
-------------+---------+----------
 usename      name    
 usesysid     integer 
 usecreatedb  boolean 
 usetrace     boolean 
 usesuper     boolean 
 usecatupd    boolean 
 passwd       text    
 valuntil     abstime 
View definition: SELECT pg_shadow.usename, pg_shadow.usesysid,
pg_shadow.usecreatedb, pg_shadow.usetrace, pg_shadow.usesuper, pg_shadow.
usecatupd, '********'::text
AS passwd, pg_shadow.valuntil FROM pg_shadow;

pg_user combines a lot of information. Let's see what is already in pg_user :

persons=#

SELECT * FROM pg_user;

usename   usesysid  usecreatedb  usetrace  usesuper  usecatupd   passwd  
valuntil
----------+----------+-------------+----------+----------+-----------+----------
+----------
 postgres        26  t            t         t         t          ********

 hans            27  f            f         f         f          ********

 epi             28  f            f         t         t          ********

(3 rows)

The users postgres , hans , and epi are already in the table. Every user has been assigned a unique user ID, and we can see in the various columns which of the most important rights a user has. We have created the user epi with the -D flag. As you can see in the previous table, the column called usercreatedb is false for user epi .

The password is not listed in plain text because this would be indeed very insecure ”everybody could read it. We can see in the definition of the view that the column containing the password is in pg_shadow . Let's try to query pg_shadow as user epi :

shop=>

SELECT * FROM pg_shadow;

ERROR:  pg_shadow: Permission denied.

User epi doesn't have the right to do that. Let's try as user postgres :

persons=#

SELECT usename, passwd FROM pg_shadow;

usename    passwd
----------+-----------
 postgres 
 hans      anypasswd
 epi       anypasswd
(3 rows)

Voil , it works for user postgres because he is the super user. We can also see the passwords now.

For creating groups, PostgreSQL does not offer a command-line tool yet. The CREATE GROUP command creates a new group account using the values specified on the command line and the default values from the system. The syntax of CREATE GROUP is as simple as the syntax of the CREATE USER command:

persons=#

\h CREATE GROUP

Command:     CREATE GROUP
Description: Creates a new group
Syntax:
CREATE GROUP name
    [ WITH
     [ SYSID gid ]
     [ USER  username [, ...] ] ]

We will create a group called cybertec that contains the users epi and hans :

CREATE GROUP cybertec WITH USER epi, hans;

There is only one line of SQL. We have to define the name of the new group and a list of all users that should be in that group.

The definition of a group is stored in the pg_group table. Look at the data structure:

persons=#

\d pg_group

Table "pg_group"
 Attribute    Type     Modifier
-----------+-----------+----------
 groname    name      
 grosysid   integer   
 grolist    integer[] 
Indices: pg_group_name_index,
         pg_group_sysid_index

The first column stores the name of the group. The second is used to store the group ID, and the array in the first column contains a list of all group members . In our case, the table contains the definition of exactly one group:

persons=#

SELECT * FROM pg_group;

groname   grosysid  grolist
----------+----------+---------
 cybertec         1  { 28,27}
(1 row)

The third column of pg_group contains the IDs of the users and not the names . The values are stored in an array of integers.

One very important thing when creating new users is that the CREATE USER command is rather badly mislabeled. CREATE USER grants super user status to new users. This can lead to extremely critical security holes in your PostgreSQL system. For example, if user postgres creates a new user, let's say the name is paul , who is allowed to create new users but nothing else, paul is allowed to create new users who have more rights than he has. This is very critical because paul could grant himself additional rights with the help of a new user.

Keep this in mind when working with user rights. Most database systems, such as Oracle, treat user rights differently and are, in this case, safer than PostgreSQL. When working with PostgreSQL, avoid creating users that have the right to create new users.

If we want to get rid of a user or a group, we can use the DROP command. Imagine a situation where we want to remove the user called testuser from our database. This can be done with the following SQL statement:

DROP USER testuser;

If we want to remove the group called testuser , we can use the following command:

DROP GROUP testuser;

Changing User and Group Attributes

Creating users and groups is not enough. After your configuration is up and running, it can easily happen that changes have to be made. Almost all basic administration tasks can be done with the ALTER command.

Before we come to the sample code and some more details, the following is an overview of the syntax of ALTER USER and ALTER GROUP :

persons=#

\h alter user

Command:     ALTER USER
Description: Modifies user account information
Syntax:
ALTER USER username
    [ WITH PASSWORD 'password' ]
    [ CREATEDB  NOCREATEDB ] [ CREATEUSER  NOCREATEUSER ]
    [ VALID UNTIL 'abstime' ]

persons=#

\h alter group

Command:     ALTER GROUP
Description: Add users to a group, remove users from a group
Syntax:
ALTER GROUP name ADD USER username [, ... ]
ALTER GROUP name DROP USER username [, ... ]

One very important thing that sometimes has to be done is changing the password of a user. The following example will show how the password of user epi can be changed to anyagain :

ALTER USER epi WITH PASSWORD 'anyagain';

ALTER USER can also be used to grant or revoke rights from a user. The next example shows how we make user kertal a little more powerful:

ALTER USER kertal WITH PASSWORD 'anyagain'


CREATEDB CREATEUSER VALID UNTIL '2005-04-12 14:22:35+02';

kertal has now the rights to create new users and databases. The only restriction is that his account is only valid until a certain date (defined by the VALID UNTIL command).

ALTER GROUP is used to add or remove users from a group. In the next example, we will remove user kertal from cybertec :

ALTER GROUP cybertec DROP USER kertal;

Let's check whether kertal is still in the group:

persons=#

SELECT * FROM pg_group;

groname   grosysid  grolist
----------+----------+---------
 cybertec         1  { 27,28}
(1 row)

kertal has been removed successfully. Because we want to re- employ kertal here at Cybertec, we will use the following command to add kertal to the group called cybertec again:

ALTER GROUP cybertec ADD USER kertal;

You can see that managing and maintaining the key attributes of users and groups are extremely easy tasks, and the whole process is as simple as maintaining user rights on a Unix system.

Using GRANT and REVOKE

This section of the book is dedicated to those who care about user rights and data security on their PostgreSQL servers. The privilege system of PostgreSQL allows the database administrator to define the abilities and rights of all users. The two most important commands when dealing with user rights are GRANT and REVOKE . GRANT is used to give a user certain rights, while REVOKE is used to take rights away from a user. GRANT and REVOKE can be used to modify the user rights of individual users, groups, and the so-called public. Public means that everybody gets or looses the right to perform a certain action.

Before we go into more detail and get to some practical examples; the following is what the database tells us about GRANT and REVOKE :

persons=#

\h GRANT

Command:     GRANT
Description: Grants access privilege to a user, a group or all users
Syntax:
GRANT privilege [, ...] ON object [, ...]
    TO {  PUBLIC  GROUP group  username }

persons=#

\h REVOKE

Command:     REVOKE
Description: Revokes access privilege from a user, a group or all users.
Syntax:
REVOKE privilege [, ...]
    ON object [, ...]
    FROM {  PUBLIC  GROUP groupname  username }

As you can see, GRANT and REVOKE need privileges and objects. Privileges are certain rights to perform certain operations.

We have compiled a complete list of all types of privileges provided by PostgreSQL:

• SELECT Allows a user to access all columns of a table or a view.

• INSERT Allows a user to perform INSERT operations on a certain table.

• UPDATE Allows a user to update data in a table.

• DELETE Allows a user to delete data in a table.

• RULE Defines rules on a table or view.

• ALL all is used to grant or revoke all these privileges at once.

Privileges are defined for objects. Objects can be tables, views, or sequences.

Before we get to some examples, we will show you how you can find what permissions are set for your objects. We will create a new table and check the permissions for that table by using the \z command:

persons=#

CREATE TABLE hackers(name varchar(50), location varchar(50));

CREATE
persons=#

\z

Access permissions for database "persons"
 Relation  Access permissions
----------+--------------------
 hackers  
(1 row)

We have created the table as user postgres . If we want to insert data as user epi , we have the required permissions to do it:

persons=#

INSERT INTO hackers VALUES ('Donald Becker',NULL);

INSERT 53792 1

If we don't want epi to insert data into that table anymore, we can revoke the permissions. In the following example, we revoke all rights on hackers from epi :

REVOKE ALL ON hackers FROM epi;

Now we connect to the database as epi :

[hs@duron hs]$

psql -U epi persons;

And now we will try to insert some data into the table:

persons=>

INSERT INTO hackers VALUES ('Alexandre Julliard',NULL);

ERROR:  hackers: Permission denied.

Because epi does not have the permission to perform the operation, the INSERT fails.

The result of the \z command looks a little different now:

persons=#

\z

Access permissions for database "persons"
 Relation  Access permissions
----------+--------------------
 hackers   { "="}
(1 row)

If epi should have the permissions to perform INSERT and UPDATE operations, we can grant them to him. This operation has to be done as user postgres , because a user can't grant rights to himself.

Note

You can grant or revoke multiple privileges with just one SQL statement. For example,

GRANT INSERT, UPDATE ON hackers TO epi;

persons=#

\z

Access permissions for database "persons"
 Relation  Access permissions
----------+--------------------
 hackers   { "=","epi=aw"}
(1 row)

persons=>

INSERT INTO hackers VALUES ('Alexandre Julliard',NULL);

INSERT 37632 1

persons=>

\d

List of relations
     Name      Type    Owner
--------------+-------+----------
 hackers       table  postgres
 kertalstable  table  kertal
(2 rows)