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:
- Create a PostgreSQL-superuser with administrator capabilities (create databases, create users, grant privileges) for ourselves.
- Create a normal PostgreSQL-user.
- 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:~$ psql template1
[...]
template1=# ALTER USER asjo WITH PASSWORD 'mysecret';
ALTER USER
template1=#
Normal user
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:
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:
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:
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.