Some (quite long) time ago, someone, somewhere (my memory is pretty fragile) asked a question. I don't have it exact, but the gist was: is it possible to give some users rights to create new users, without making them superusers, and forcing new users to have access only to one particular database.
After some discussion it was clear that the scenario was shared hosting with PostgreSQL, so the situation could look like this:
you are administrator of shared hosting service. One of services is PostgreSQL. You have client, named “depesz", and you want him to be able to create new users, but these users shouldn't be able to connect to any other database than depesz's db.
Is it doable?
First problem is that if we'd give our client privilege to create user – he will get “superuser" privileges:
$ CREATE USER client; CREATE ROLE $ ALTER USER client WITH createuser; ALTER ROLE $ SELECT * FROM pg_authid WHERE rolname = 'client'; -[ RECORD 1 ]--+------- rolname | client rolsuper | t rolinherit | t rolcreaterole | f rolcreatedb | f rolcatupdate | t rolcanlogin | t rolreplication | f rolconnlimit | -1 rolpassword | [NULL] rolvaliduntil | [NULL]
So, it looks like a no go. But is it really? Perhaps we could make a function that would make it work? Client would call the function, and the function would do the job. Usually functions run with the privileges of user that is executing them, but we have the cool feature of “SECURITY DEFINER" functions.
With this in mind, let's write down some requirements.
- every user has to be created with password
- only owners of a database can create new users
- created user will have only access to the database it was created in/for
Let's start from the end. Making sure user has no rights to connect to all (except some) databases can be done in 2 ways:
- revoke privileges to connect to every database from given user
- revoke privileges to connect to every database from “public"
I prefer the 2nd solution, because it's easier – with 3 databases, and 10 users, we would have to issue 27 revokes in #1, but only 3 in #2, plus 3 grants.
It would be great if we could simply wrap it all in stored procedure, but unfortunately PostgreSQL (as of now) doesn't let you run create database in function or transaction.
Due to this limitation, we have to either use dblink (which we don't want in such a simple tutorial), or just write all the commands ourselver.
Whenever we will have new client, we will have to run 3 commands:
$ CREATE USER whatever WITH PASSWORD 'xxx'; $ CREATE DATABASE whatever WITH OWNER whatever; $ REVOKE ALL ON DATABASE whatever FROM public;
Thanks to this we will have specialized user being owner of the database, and no other user (except superusers of course) will be able to connect to this database.
It we have any other databases, we should revoke public login too, with simple query:
$ DO LANGUAGE plpgsql $$ DECLARE temprec record; BEGIN FOR temprec IN SELECT datname FROM pg_database WHERE datallowconn LOOP EXECUTE 'REVOKE ALL ON DATABASE ' || quote_ident( temprec.datname ) || ' FROM public'; END LOOP; END; $$;
Afterwards we have all databases secured to the point where only owner of a database can connect to it (and superusers).
Now. What about additional users? Since user creation can be done in function, we can write something like this:
CREATE OR REPLACE FUNCTION create_new_db_user (p_login TEXT, p_password TEXT ) RETURNS void AS $$ DECLARE v_login TEXT := quote_ident( p_login ); v_owner TEXT; BEGIN -- It should work for db owner only SELECT u.usename INTO v_owner FROM pg_database d JOIN pg_user u ON d.datdba = u.usesysid WHERE d.datname = current_database(); IF v_owner IS DISTINCT FROM session_user THEN RAISE EXCEPTION 'Only database owner (%) can run this function.'; END IF; EXECUTE 'CREATE USER ' || v_login || ' WITH PASSWORD ' || quote_literal( p_password ); EXECUTE 'GRANT CONNECT, TEMPORARY ON DATABASE ' || quote_ident( current_database() ) || ' TO ' || v_login; RETURN; END; $$ LANGUAGE plpgsql SECURITY DEFINER;
This function, when created in client database, gives the user ability to create new users, which can connect only to their database:
(whatever@[LOCAL]:5910) 17:03:02 [whatever] $ \du List OF roles ROLE name | Attributes | Member OF -------------------------+------------------------------------------------+----------- depesz | Superuser, Replication | {} pgdba | Superuser, CREATE ROLE, CREATE DB, Replication | {} postgres | Superuser, Replication | {} whatever | | {} (whatever@[LOCAL]:5910) 17:03:10 [whatever] $ SELECT CURRENT_USER, session_user; CURRENT_USER | session_user --------------+-------------- whatever | whatever (1 ROW) (whatever@[LOCAL]:5910) 17:03:34 [whatever] $ SELECT create_new_db_user( 'xxx', 'pass' ); create_new_db_user -------------------- (1 ROW) 17:03:48 depesz@h3po4 ~ =$ psql -U xxx -d whatever psql (9.2devel) TYPE "help" FOR help. (xxx@[LOCAL]:5910) 17:03:56 [whatever] $
As you can see user “xxx" can now connect to database whatever, while it cannot connect to others:
=$ psql -U xxx -d postgres psql: FATAL: permission denied FOR DATABASE "postgres" DETAIL: USER does NOT have CONNECT privilege.
To make it work sensibly, we'll need to create this function in all databases. For new databases (ones that will be created in future) it's enough to create it in template1, but in others – we have to do it “manually".
Thankfully psql can help quite a bit.
Let's assume the file with function definition is in /tmp/function.sql file.
So, now, I'll login to pg as super user, and will:
(postgres@[LOCAL]:5910) 17:09:21 [postgres] $ \pset format unaligned Output format IS unaligned. (postgres@[LOCAL]:5910) 17:09:24 [postgres] $ \pset tuples_only Showing ONLY tuples. (postgres@[LOCAL]:5910) 17:09:32 [postgres] $ \o /tmp/loader.sql (postgres@[LOCAL]:5910) 17:09:38 [postgres] $ SELECT E'\\c ' || quote_ident(datname) || E'\n\\i /tmp/function.sql' FROM pg_database WHERE datallowconn; (postgres@[LOCAL]:5910) 17:09:40 [postgres] $ \o (postgres@[LOCAL]:5910) 17:09:41 [postgres] $ \i /tmp/loader.sql You are now connected TO DATABASE "whatever" AS USER "postgres". CREATE FUNCTION You are now connected TO DATABASE "qwerty" AS USER "postgres". CREATE FUNCTION You are now connected TO DATABASE "rt" AS USER "postgres". CREATE FUNCTION You are now connected TO DATABASE "template1" AS USER "postgres". CREATE FUNCTION You are now connected TO DATABASE "postgres" AS USER "postgres". CREATE FUNCTION You are now connected TO DATABASE "depesz" AS USER "postgres". CREATE FUNCTION You are now connected TO DATABASE "depesz_explain" AS USER "postgres". CREATE FUNCTION You are now connected TO DATABASE "pgdba" AS USER "postgres". CREATE FUNCTION (postgres@[LOCAL]:5910) 17:09:46 [pgdba] $
/tmp/loader.sql file contains (in my database):
=$ cat /tmp/loader.sql \c whatever \i /tmp/function.sql \c qwerty \i /tmp/function.sql \c rt \i /tmp/function.sql \c template1 \i /tmp/function.sql \c postgres \i /tmp/function.sql \c depesz \i /tmp/function.sql \c depesz_explain \i /tmp/function.sql \c pgdba \i /tmp/function.sql
Of course after it got executed both /tmp/loader.sql and /tmp/function.sql can be removed.
ugh… the fact that you would need to do this kills me… dear postgres please add role’s for create database create user that can be assigned to regular users like other roles.
I think you need CREATEROLE rather than CREATEUSER. See: http://www.postgresql.org/docs/9.0/interactive/sql-createrole.html
Of course, there still may be a place for a security definer function to arrange the proper permissions for created roles.
@Caleb, the issue with Postgres is that by default all users/roles are shared between all databases. Also if you grant the right to create roles, the user can create any role they want. So that is why you end up wanting to lock down how people create users to meet the security restrictions you want.