Creating Accounts in Postgres

Many people new to Postgres are confused by the security model. They understand the SQL commands that use GRANT, CONNECT and REVOKE to alter permissions (the SQL DSL, or Data Security Language component of SQL) because that is common across various SQL Database Engines. However, Postgres has another layer of security that you need to successfully navigate first.

== pg_hba.conf ==

Postgres uses a configuration file, called the pg_hba.conf file, that identifies how each user (or users) can connect to each database in the postgres engine. It identifies what system (or systems) the user(s_ can connect from what systems and also specifies the authentication method they use for that database. In wriitng (and revising this), it sounds complex, or maybe it's just my style of writing. But the below description should make it clearer.

Basically there are four fields that are checked:

• Username
• Database
• User connection source
• Authentication mechanism

The sample file below (with explanation) may help understand the different methods and sources:

local     all         postgres         ident
local     music_db    music            md5
local     all         dbadump          trust
#local     all         all              ident sameuser
local     all         all              ident

This means that the user _postgres_ can connect locally (over a Unix Socket) to any database, and you will trust that the Operating System has already identified the user (the ident method).

The user _music_ can connect locally to the database music_db using a password encrypted in postgres (the password is encrypted using md5).

The user _dbadump_ can connect locally to any database and is trusted into that database. This is a potential security issue as ANY user on the local system can claim to be the user _dbadump_ and connect to any database. (You'd have to rely on SQL DSL and GRANT permissions to restrict their access at this point!)

The next line is a comment. So it is ignored by engine, but you can use it to document your configuration file if you so wish.

The last line says that any other system user (Linux or Windows) can connect locally to any database (again, their permissions should be controlled through DSL inside each database).

One thing to note (and is specified in the Postgres documentation) is the permissions should be going from MOST restrictive (the tightest definition) to the LEAST restrictive (the potential for more people to match the "rule"). The reason is that the first rule that matches (whether they successfully log in or not!) determines which rule APPLIES (and no further checking occurs!).

Now that I've shown what happens when you connect locally (over a Socket), we'll talk about REMOTE (from another computer) connections.

# IPv4 local connections:
host      all         all         127.0.0.1/8           md5

Any user that connects to any database using localhost (127.0.0.1 to 127.255.255.255) will use an encrypted password stored in Postgres.

A word of warning on this one - I've used the network of 127.*.*.* instead of just 127.0.0.1/32 because some Linux distributions define the localhost (or even the hostname) with something else in the 127.*.*.* network. If you choose to use a CIDR Address of 127.0.0.1/32 or 127.0.0.1/24 (anything between 127.0.0.1 and 127.0.0.255) you could exclude some addresses where they would connect with a different rule.

host      music_db    music       192.168.1.53/32       md5
host      music_db    music       192.168.1.55/32       md5
host      all         timothy     192.168.1.1/24        md5
host      all         dbauser     192.168.1.50/32       trust
hostssl   all         admin       1.1.1.1/0             md5

The user _music_ can connect to the database music_db from either 192.168.1.53 or 192.168.1.55 (and they can authenticate or prove who they are by providing a password that matches their password stored in postgres and encrypted using the md5 algorithm).

The user _timothy_ can connect to ANY database from any computer with an IP Address between 192.168.1.1 and 192.168.1.255. (NOTE: This is only on the local network. If you connect over the Internet, which I will show later, you will have a different EXTERNAL IP Address that the database engine will see.)

The user _dbauser_ can connect to any database from the IP Address of 192.168.1.50 ONLY. But any user that claims to be _dbauser_ on that system will be explicityly trusted into the engine.

The user _admin_ can connect from ANY IP Address in the world and will be able to prove who they are by providing a password that matches up with what is already encrypted and stored inside Postgres. The only difference between this and previous "host" entries is that the connection is encrypted using SSL (Secure Sockets Layer). There is additional overhead associated with using encryption, but especially across the Internet, you really should encrypt the whole session (especially the authentication piece so you don't send a password across the Internet in plain text).

The last line is similar, but it authenticates against a LDAP souce rather than having the password stored in postgres.

== Roles ==

Once the user has a connection to the server/database, a second mechanism is used to identify what objects they can access, basically where they have permissions to view or update data. This is the Data Security Language (DSL) portion of SQL. This is where you allow a user to CONNECT to a database or read data (SELECT permission) or update data (UPDATE or DELETE permissions). These permissions are well explained in the documentation for Postgres or any other SQL Database engine.