PostgreSQL User Authentication and Authorisation

PostgreSQL has two levels of authorisation, one at the database level, called host based authentication, and one at a finer level on tables, views and sequences.

Host-Based Authentication using pg_hba.conf

The host-based authentication is controlled by the pg_hba.conf file and defines which users can connect to which database and how they can connect to it. The file is a list of declarations, which are searched in order until one of the lines match. They list the access method, the database they are trying to connect to, the user trying to connect and the authentication method being used.

Access methods

There are three different access methods:

local

This is for a user connecting via the unix socket on the local machine. A line for this method will be in the form:

local      DATABASE  USER  METHOD [OPTION]

host

This is matches connections over a TCP/IP network connection

host       DATABASE  USER  IP-ADDRESS  IP-MASK   METHOD  [OPTION]
host       DATABASE  USER  IP-ADDRESS/CIDR-MASK  METHOD  [OPTION]

hostnossl, hostssl

This is for users connecting over a non-encrypted or an encrypted TCP/IP connection using SSL. This is so that you can treat secure and non-secure connections differently. For example you might be happy to have clear text passwords over SSL, but only allow MD5 over non-secure connections.

hostnossl  DATABASE  USER  IP-ADDRESS  IP-MASK   METHOD  [OPTION]
hostnossl  DATABASE  USER  IP-ADDRESS/CIDR-MASK  METHOD  [OPTION]
hostssl    DATABASE  USER  IP-ADDRESS  IP-MASK   METHOD  [OPTION]
hostssl    DATABASE  USER  IP-ADDRESS/CIDR-MASK  METHOD  [OPTION]

You can list several databases by separating them by commas. 

There are two special database names, all and sameuser. all allows the person to connect to all databases on the server. sameuser allows the user to connect to a database with the same name as the user connecting. You can also supply a filename which lists databases they can connect to by using @filename where filename is a file in the same directory as the pg_hba.conf.

You can also list several users by separating them by commas. You can specify groups by prefixing the name with a +. Again you can use a filename with users in by using @filenamewhere filename is a file in the same directory as pg_hba.conf. There is the special usernameall, which matches any user.

Authentication Methods

trust

This method allows any user to connect without a password. This should be avoided unless you know what you are doing as it could be a security risk.

reject

This is the reverse of trust, as it rejects any one. This is particularly useful where you want to enable access to a range of addresses, but want to block a particular host in that range.

host  sales    alice 10.0.0.128 255.255.255.255 reject
host  sales    alice 10.0.0.0 255.255.0.0 md5

password

This method allows someone to connect providing they have given the correct password for their user in the pg_shadow table. If the password field in that table is null, then they will be rejected. The password is sent in cleartext, so you probably only want to enable this for hostssl connections.

crypt

This is like password, except it is encrypted with the trivial unix crypt encryption. This is not a very strong encryption, so I suggest you don’t use this.

md5

This is like crypt and password, except it uses the stronger MD5 to encrypt the password. If you have to use non-ssl connections, I recommend you using this method.

ident

With network connections, this method uses the ident protocol (RFC1413) to check which user on the remote system owns the network connection being used to talk to the server. This is easy to spoof, so you shouldn’t reply on this for unsecured networks. I would recommend against it on all networks.

With local connections, it uses the unix user connecting to the unix socket and is much more secure. This allows local users to connect without a password.

This is the only method that requires an option, which is the name of a map in pg_ident.conf, which maps remote users to postgresql users. The format of pg_ident.conf is:

map unixuser  postgresuser

This allows you to have the same remote user map to different postgres users when they connect to different databases,

There is a special map name called sameuser, which uses the same remote username for the postgresql name.

krb4, krb5

These allow you to use kerberos authentication.

pam

This method allows you to authenticate users against the local system’s pam (pluggable authentication modules) subsystem.

For local connections, I would recommend ident, password, crypt or md5. For hostssl connections any of the password methods will work, but md5 is preferable. For host and hostnossl, I can only recommend md5 and hostssl should be used in preference to host and hostnossl.

   local    all      postgres                         ident sameuser
   hostssl  all      postgres    0.0.0.0 0.0.0.0      md5

   local    sameuser all                              ident sameuser
   hostssl  sameuser all      10.0.0.0 255.255.255.0  md5

   hostssl  sales    alice    10.0.0.1/32             md5

Permissions

Every object (tables, views and sequences) have an owner, which is the person that created it. The owner, or a superuser, can set permissions on the object. Permissions are made up of a user or group name and a set of rights. These rights described in the table below.

Library versions

Privilege	short name	Description
SELECT	r	Can read data from the object.
INSERT	a	Can insert data into the object.
UPDATE	w	Can change data in the object.
DELETE	d	Can delete data from the object.
RULE	R	Can create a rule on the table
REFERENCES	x	Can create a foreign key to a table. Need this on both sides of the key.
TRIGGER	t	Can create a trigger on the table.
TEMPORARY	T	Can create a temporary table.
EXECUTE	X	Can run the function.
USAGE	U	Can use the procedural language.
ALL		All appropriate privileges. For tables, this equates to arwdRxt

You can apply these privileges to users, groups or a special target called PUBLIC, which is any user on the system.

Viewing privileges

You can view permissions using the \z command in psql. You can use \d to view the owner.

 sales=# \dp
                                       Access privileges for database "sales"
 Schema |           Table            |       Access privileges
--------+----------------------------+-------------------------------------
 public | inventory                  | {postgres=a*r*w*d*R*x*t*/postgres,
                                        bob=rw/postgres,
                                        "group sales=rwd/postgres"}
 public | inventory_inventory_id_seq |
 public | suppliers                  | {postgres=a*r*w*d*R*x*t*/postgres,
                                        alice=arwdRxt/postgres,
                                        bob=r/postgres}
 public | suppliers_supplier_id_seq  |
(4 rows)

In this example, we can see that postgres has all privileges to both tables. Bob has read and write and sales group has read, write and delete on the inventory table. Alice has all privileges and bob has read on the suppliers table. The * for postgres means that they have the privilege to grant that privilege. The /postgres tells you who granted those privileges.

Adding privileges

You can assign privileges using the GRANT command.

GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
    [,...] | ALL [ PRIVILEGES ] }
    ON [ TABLE ] tablename [, ...]
    TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

The WITH GRANT OPTION allows you give the person you are granting the privileges the ability to grant that privilege themselves. We can give bob the ability to make any changes to the data in suppliers using:

GRANT INSERT, UPDATE, DELETE ON TABLE suppliers TO bob;

Removing privileges

You can also remove privileges using the REVOKE which has the same syntax as theGRANT.

REVOKE [ GRANT OPTION FOR ]
    { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
    [,...] | ALL [ PRIVILEGES ] }
    ON [ TABLE ] tablename [, ...]
    FROM { username | GROUP groupname | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

GRANT OPTION FOR allows you to remove the ability to grant privileges to others, and not the privileges themselves. Suppose you want to remove privileges from bob, and anyone he has granted it to, we can use the CASCADE option.

REVOKE INSERT UPDATE DELETE ON TABLE suppliers FROM bob CASCASE

Column Level Privileges

PostgreSQL doesn’t directly support privileges at the column level but you can fake the, using views. To do this, you create a view with all the columns you want that person to see and grant them privileges to view that view.

Changing Ownership

It is possible to change the ownership of objects using the ALTER TABLE:

ALTER TABLE suppliers OWNER TO bob;

This can be time consuming to do if you have a lot of tables. A quicker, but possibly dodgy way to fix this is to use the following untested SQL command. You need to set relowner to the sysid of the new owner, which you can find by checking pg_shadow.

 UPDATE pg_class SET relowner = 100
   WHERE pg_namespace.oid = pg_class.relnamespace
   AND pg_namespace.nspname = 'public';

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