Hi,
we are hosting several customers and we wish to give them access to
databases. Currently they have Access, and we wish to add PostgresQL,
which we so far have used only for ourselves. However, we have some
doubts about setting up the system security.
The plan is to give every customer his own database & login using the
function below:
DROP FUNCTION fn_create_database(name, text);
CREATE FUNCTION fn_create_database(name, text) RETURNS VARCHAR AS '
DECLARE
a_output VARCHAR(4000);
b_output VARCHAR(4000);
c_output VARCHAR(4000);
BEGIN
a_output := ''CREATE DATABASE '' || $1;
b_output := ''CREATE USER '' || $1 || ''_admin WITH PASSWORD ''''''
|| $2 || '''''''';
c_output := ''UPDATE pg_database SET datdba = (SELECT usesysid FROM
pg_user WHERE usename = '''''' || $1 || ''_admin'''') WHERE datname =
'''''' || $1 || '''''''';
EXECUTE a_output;
EXECUTE b_output;
EXECUTE c_output;
END;
' LANGUAGE 'plpgsql';
Installed procedural languages are internal, C, sql and plpgsql, but
only plpgsql is trusted.
Does anybody see any problems with this setup securitywise? Specifically:
- can users break out of PostgresQL to execute shell commands?
- is it a security risk to give users RULE permission?
- is it a security risk to make sql a trusted language?
And as the bonus question:
- how would I grant select permissions to a user on tables in a specific
database, while connected (as superuser) to a different database in the
same cluster? I suppose it requires manipulating pg_class, any suggestions?
Running PostgreSQL 7.1 on i386-unknown-openbsd2.9, compiled by GCC 2.95.3
Jochem
