PostgreSQL Roles and Login Privileges

Published on: 13 Sep 2017

I’m currently working on a project with another developer and because the
database is hosted on my server I needed to create a new role with a new
password and most importantly restrict their access to my other databases (the
ones I use for my personal projects). I trust them to not do anything malicious
because they’re friends but what if you’re involved in a project where you don’t
know all the collaborators that well ? Better safe than sorry.

In this short post, I will detail how I managed to solve both of the above mentioned
problems.

1. Give a user a password

To Add or Change a Postgresql user’s password, use the following statement:

_EDIT: From what I understand, and based on my experiments and research. You
still have to pass the -h (Host) flag. For postgres to consider it a network
connection. If you don’t, you may or may not trigger the infamous ‘peer
authentication failed for user '_

2. Setup Login Privileges

To filter which users can login to a given database, run the following SQL
commands:

REVOKECONNECTONDATABASEdb_nameFROMPUBLIC:

This will revoke login for all users who inherit the PUBLIC role. (Which, to
my knowledge, is pretty much everyone).

To allow login for a given user, execute this statement:

GRANTCONNECTONDATABASEdb_nameTOrole_name;

If you want this setting to apply to all future databases:

REVOKECONNECTONDATABASEtemplate1FROMPUBLIC:

Note: template1 is the default template used for creating new databases

A note on Postgresql Privileges

As I covered in the previous paragraphs, it’s possible to give and restrict
certain privileges to users (or roles) in postgresql. These so called priveleges
are of numerous types:

SELECT

UPDATE

INSERT

DELETE

CREATE

TRIGGER

CONNECT

TEMPORARY

EXECUTE

USAGE

ALL

superuser roles and database owners can grant and revoke the above listed
privileges as they wish using the GRANT and REVOKE keywords.

Let’s look at a few examples:

To prevent a given user from interacting directly with the database, run the
following statement:

REVOKESELECT,UPDATE,INSERT,DELETEONtable_nameFROMrole_name;

To allow a user to perform all actions on a database, you can run this
statement:

GRANTALLONtable_nameTOrole_name;

Conclusion

I hope this was helpful to you in some way. Now you know how to change (or add)
a PostgreSQL user password and how to limit a Postgresql user’s access to your
databases using privileges.
If I missed something or if you have any questions / remarks etc, give me a
shout on twitter, I’m @zabanaa