PostgreSQL Permissions and Roles

From Seven
Jump to: navigation, search

Cheat sheet for PostgreSQL

Creating New Users

Creating powerful super users in PostgreSQL is pretty straight forward. For security reasons it is advisable to revoke (or never grant) SUPERUSER privileges to accounts used by other applications, for example highly hackable ones accessible from the evil Internet. Therfore a Mapbender installation should never use a SUPERUSER to access the database. Instead create the database with a SUPERUSER but then only allow limited access by creating another regular user who is only allowed to read, update and delete data from this particular database. In this example we will create a user mapbender_user and a database mapbender_data with the SUPERUSER postgresl using the command line client psql.

CREATE USER mapbender_user;
ALTER ROLE mapbender_user PASSWORD 'secret_password';

If you created mapbender_user with CREATEDB or SUPERUSER privileges:

ALTER ROLE mapbender WITH CREATEDB;
ALTER ROLE mapbender WITH SUPERUSER;

you should revoke them after installation:

ALTER ROLE mapbender NOSUPERUSER;
ALTER ROLE mapbender NOCREATEDB;

Granting Connect to Database

A new naked user is not allowed anything in PostgreSQL, so first allow to connect with the database:

GRANT CONNECT ON DATABASE mapbender_data TO mapbender ;

Granting permissions for multiple tables in PostgreSQL

Then allow the user to read and write to all tables of the new database. PostgreSQL does not allow to GRANT permissions to multiple tables using wildcards. Therfore this pseudo-code does not work:

SELECT 'GRANT, INSERT, UPDATE, DELETE ON * TO mapbender;

But the list of tables can be retrieved from catalog tables. In this example we select all tables from the public schema of the database mapbender_data. The result of this query is then concatenated with the GRANT SQL commando and piped back to psql. This will essentially do the same thing as we wanted to do with the pseudo code above and grants INSERT, UPDATE, DELETE to the mapbender_user.

Tables
psql -d mapbender_data -qAt -c "SELECT 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || tablename || ' 
TO mapbender_user;' FROM pg_tables WHERE schemaname = 'public'" | psql -d mapbender_data
Views
psql -d mapbender_data -qAt -c "SELECT 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || viewname || ' TO mapbender_user;' FROM pg_views where schemaname = 'public'" | psql -d mapbender_data
Sequences?

Use this code if you want to allow a user with limited permissions (not OWNER of the database or SUPERUSER) to be able to use the Mapbender database.