Postgresql User Administration

Adding users

There are two ways of adding users in PostgreSQL; 

• the command line createuser tool
• using SQL. 

The command line is the easier of the two methods.

Becoming a superuser

To add a user you need to use a postgres user with the ability to add users (a superuser). In most cases this will be the postgres user, which is the initial superuser. 

The simplest way to connect as the postgres user is to change to the postgres unix user on the database server and take advantage of postgres’ ident based authentication, which trusts your unix account. Ident and other connection schemes are explained below.

When you add a user you can opt to give the new user two additional powers; the ability to create new databases and the ability to create new users. 

The createuser command allows you to set these using command line options. -a allows the user to add new users and -A prevents them from doing so. Likewise -d and -D allows or disallows them from creating databases. The other option you will probably want to use is the -p flag to ask for a password for the new user. (This is important if you intend to use password based authentication, as if you do not give a password, it will be NULL and all passwords will be rejected. If you don’t give any options it will prompt you for them.)

You can also use the CREATE USER SQL command:

CREATE USER username [ [ WITH ] 
   SYSID uid 
   | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
   | CREATEDB | NOCREATEDB
   | CREATEUSER | NOCREATEUSER
   | IN GROUP groupname [, ...]
   | VALID UNTIL 'abstime' ] 

You can also add a user to an existing group and specify a date when the user’s password will expire. The sysid is like the unix uid number and postgres will pick a suitable default.

Listing users

You can see the users on the server by selecting from the pg_shadow system table. If you are not a super user, you will not have permission to access this table and will have to access the pg_user view instead, which is identical, but displays the password as stars.

postgres=# select * from pg_shadow;

usename  | usesysid | usecreatedb | usesuper | usecatupd | userepl |               passwd                | valuntil | useconfig 

----------+----------+-------------+----------+-----------+---------+-------------------------------------+----------+-----------

 postgres |       10 | t           | t        | t         | t       | md515edd46c4dff4b9f2624a1e2a5b7fb61 |          | 

 huangs   |    16386 | t           | t        | t         | f       | md5ff059b95edd295106b5bdb6053407706 |          | 

(2 rows)

Altering users

If you want to change a user you can use the ALTER USER SQL command, which is similar to the CREATE USER command except you can’t change the sysid.

ALTER USER name [ [ WITH ] 
    [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' 
    | CREATEDB | NOCREATEDB
    | CREATEUSER | NOCREATEUSER 
    | VALID UNTIL 'abstime' ]

Changing a user password

One of the most common reasons for wanting to alter a user is to change the user’s password

postgres=# ALTER USER colin WITH PASSWORD 'letmein';
ALTER USER

Removing users

Just like creating users, there are two ways to remove users, using the command line or SQL. At the command line we would use:

 $ dropuser alice
DROP USER

or using SQL:

postgres=# DROP USER colin;
DROP USER

source: http://www.davidpashley.com/articles/postgresql-user-administration/