Thread: PostgresQL setup in hosted environment

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

Jochem van Dieten <jochemd@oli.tudelft.nl> writes:
> - can users break out of PostgresQL to execute shell commands?

Not if you don't make them superusers, which your script doesn't.
(If they are superusers, they can install C functions that do anything
the Postgres userid can do.)

> - is it a security risk to give users RULE permission?

No.

> - is it a security risk to make sql a trusted language?

It's irrelevant; the TRUST flag is only used for procedural languages.

> - 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?

You wouldn't.

            regards, tom lane

Tom Lane wrote:

> Jochem van Dieten <jochemd@oli.tudelft.nl> writes:
>
>>many questions
>
> many answers


Thanx.

>>- 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?
>>
>
> You wouldn't.


I was under the impression that the relacl field in the pg_class table
was just an array, so I could add items there. Could you (or anyboby
else) elaborate on the field type of this field (or its function if that
is the part I am misunderstanding)?

Jochem

Jochem van Dieten <jochemd@oli.tudelft.nl> writes:
> - 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?
>
>> You wouldn't.

> I was under the impression that the relacl field in the pg_class table
> was just an array, so I could add items there.

You could do that (or just use GRANT/REVOKE to do it for you) ... *if*
you were in the right database.  Since you're not, you have no access to
the pg_class table that you'd need to modify.  Each database has its own
pg_class.

            regards, tom lane