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.
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.
- 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
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.
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.
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;
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
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.
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/
