PostgreSQL User Administration

PostgreSQL has a powerful user authentication and access control system, but for a newcomer, it can seem confusing and overwhelming. In this article I hope to explain how to manage user and permissions with ease.

User Administration

Adding users

There are two ways of adding users in PostgreSQL; the command line createuser tool or by 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 -Aprevents 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.

Note

The ability to create new users is slightly misdescripted. It actually makes the user a super user, with the ability to do anything in postgres including the ability to create new databases, so createuser -D -a user doesn’t make sense.
You can also use the CREATE USER SQL command, which has a few options now available with the createuser command.
 CREATE USER username [ [ WITH ] 
   SYSID uid 
   | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
   | CREATEDB | NOCREATEDB
   | CREATEUSER | NOCREATEUSER
   | IN GROUP groupname [, ...]
   | VALID UNTIL 'abstime' ]
The simplist form is:
CREATE USER user;
which will create a user with no password and no extra privileges. 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.
template1=# CREATE USER alice WITH PASSWORD 'pAssw0rd';
CREATE USER
template1=# CREATE USER bob VALID UNTIL 'Jan 31 2030';
CREATE USER

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.
template1=# select * from pg_shadow;
 usename  | usesysid | usecreatedb | usesuper | usecatupd |               passwd                |        valuntil        | useconfig
----------+----------+-------------+----------+-----------+-------------------------------------+------------------------+-----------
 postgres |        1 | t           | t        | t         |                                     |                        |
 alice    |      101 | f           | f        | f         | md55f85af706c9e04a6ebc02a5501f6bfe3 |                        |
 bob      |      102 | f           | f        | f         |                                     | 2030-01-31 00:00:00+00 |
(4 rows)

template1=# select * from pg_user;
 usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  |        valuntil        | useconfig
----------+----------+-------------+----------+-----------+----------+------------------------+-----------
 postgres |        1 | t           | t        | t         | ******** |                        |
 alice    |      101 | f           | f        | f         | ******** |                        |
 bob      |      102 | f           | f        | f         | ******** | 2030-01-31 00:00:00+00 |
(4 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' ]
Say we wanted to allow alice to create databases:
 template1=# ALTER USER alice CREATEDB;
ALTER USER
You can also rename users using:
ALTER USER name RENAME TO newname;
To rename bob to colin we could use:
 template1=# ALTER USER bob RENAME TO colin;
ALTER USER

Changing a user password

One of the most common reasons for wanting to alter a user is to change the user’s password
 template1=# ALTER USER colin WITH PASSWORD 'letmein';
ALTER USER
Checking pg_users again, we can see the changes:
 alice    |      101 | t           | f        | f         | ******** |                        |
 colin    |      102 | f           | f        | f         | ******** | 2030-01-31 00:00:00+00 |

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:
 template1=# DROP USER colin;
DROP USER

Groups

Groups are entirely optional in postgresql. They are only used to simplify granting and revoking privileges for the db admin, and users do not need to be a member of any group.

Creating Groups

Unlike creating users, you can only create groups using SQL. The command is:
CREATE GROUP name [ [ WITH ] 
     SYSID gid
     | USER  username [, ...]]
If we wanted to create a group with alice as an initial member, we can use:
 template1=# CREATE GROUP sales WITH USER alice;
CREATE GROUP

Adding or removing users from groups

You can add or remove users from groups after they have been created using the ALTER GROUP command:
 ALTER GROUP groupname [ADD|DROP] USER username [, ...  ]
Imagine we wanted to add bob to the sales group and remove alice:
template1=# ALTER GROUP sales ADD USER bob;
ALTER GROUP
template1=# ALTER GROUP sales DROP USER alice;
ALTER GROUP

Viewing groups

We can see group membership by viewing the pg_group system table. In this example I’ve added alice back into the sales group.
template1=# select * from pg_group ;
 groname | grosysid | grolist
---------+----------+---------
 sales   |      100 | {102,101}
(1 row)
The grolist column shows a list of user ids that are in the group. If you want to see the usernames in a particular group you can use:
template1=# select usename from pg_user, (select grolist from pg_group where groname = 'sales') as groups where usesysid = ANY(grolist) ;
 usename
---------
 alice
 bob
(2 rows)

Renaming Groups

You can also rename groups using:
ALTER GROUP groupname RENAME TO newname
To rename sales to presales we would use:
template1=# ALTER GROUP sales RENAME TO presales;
ALTER GROUP

Removing Groups

Removing groups can be done using DROP GROUP
template1=# DROP GROUP presales;
      DROP GROUP

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';
 
https://severalnines.com/blog/postgresql-privileges-user-management-what-you-should-know