koldfront

PostgreSQL access-control how-to

Preface

Installing PostgreSQL is easy on most GNU/Linux-systems. Just install the appropriate packages; there you go.

Usually when you install a database, you want to get started right away with creating tables, making stuff work. But, you need to create the appropriate access-control rules.

This is often not easy when you're just starting out, understanding this new systems jargon, how it works - and it's probably not what you are interested in spending time on right away, so most of the time you end up with no or very little access control. Not so good.

This how-to aims to be very specific about how to get simple, usable access-control set up for PostgreSQL in the first 5 minutes after you installed it.

Please notice: This how-to is based on how the Debian PostgreSQL-packages are set up and run. YMMV.

Installing

On Debian GNU/Linux it is as simple as: apt-get install postgresql postgresql-client postgresql-doc

PostgreSQL runs as the user "postgres", the configuration-files are located in /etc/postgresql/ and the commandline-utility "psql" can be used to connect to databases and enter SQL-commands.

Goal

We want to do three things:

  1. Create a PostgreSQL-superuser with administrator capabilities (create databases, create users, grant privileges) for ourselves.
  2. Create a normal PostgreSQL-user.
  3. Preserve access to PostgreSQL for the maintenance-scripts that Debian per default runs for PostgreSQL.

The superuser and the normal user must both authenticate with a password to PostgreSQL.

Superuser

Create the user, then set the password.

postgres@topper:~$ createuser asjo
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) y
CREATE USER
postgres@topper:~$ psql template1
[...]
template1=# ALTER USER asjo WITH PASSWORD 'mysecret';
ALTER USER
template1=#

Normal user

postgres@topper:~$ createuser wwwdata
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER
postgres@topper:~$ psql template1
[...]
template1=# ALTER USER wwwdata WITH PASSWORD 'somepassword';
ALTER USER
template1=#

Password authentification

In /etc/postgresql/pg_hba.conf, these lines should be in effect:

local all postgres ident sameuser
local all all md5
host all all 127.0.0.1 255.255.255.255 md5
host all all 0.0.0.0 0.0.0.0 reject

Which means: the unix-user "postgres" is allowed to login as the PostgreSQL-user "postgres" (via unix-socket). All other users must authenticate via password (via unix-socket or TCP/IP on 127.0.0.1).

Preserving access for maintenance-scripts

Is done by having the first line in pg_hba.conf above give the unix-user postgres access to all databases as the PostgreSQL-user postgres.

Testing that it works

Superuser

Should only be able to access a database with a password:

asjo@topper:~$ psql template1
Password:
Welcome to psql 7.4.6, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

template1=> \q
asjo@topper:~$

Normal user

Should only be able to access a database with a password:

asjo@topper:~$ psql -U wwwdata template1
Password:
Welcome to psql 7.4.6, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

template1=> \q
asjo@topper:~$

"postgres" user

Should still be able to access a database without a password:

asjo@topper:~$ sudo -u postgres psql -U postgres template1
Welcome to psql 7.4.6, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

template1=# \q
asjo@topper:~$ psql -U postgres template1
psql: FATAL: IDENT authentication failed for user "postgres"
asjo@topper:~$

What not to do

I don't know by now...

Passwords

When you get tired of keying in a password every time you start psql, you can set up a file called ~/.pgpass with lines in the format hostname:port:database:username:password where either of the first fields can be '*' meaning 'any'.

This file must only be readable by oneself.

Comments and suggestions

Are much appreciated - send me an email.

0.0021 s
webcustodian@koldfront.dk