Create readonly user on all 1000 postgres databases and schema's

Question

Using Vault I'm trying to create an on-demand temporary read-only users in a Postgres 11.8 instance.

I will have:

1000+ databases (one per customer aka tenant)
Each database has a public and reporting schema.

So I'm trying to find a way to grant this read-only user access to every database, and on all tables in both schemas.

While I came up with the following snippet:

-- Create a new user
CREATE ROLE "my-readonly-user" WITH LOGIN PASSWORD 'test123';

-- Grant access to the two schema's we have
GRANT USAGE ON SCHEMA public TO "my-readonly-user";
GRANT USAGE ON SCHEMA reporting TO "my-readonly-user";

-- Grant access to all tables in our two schema's
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "my-readonly-user";
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO "my-readonly-user";

-- Grant access to sequences
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO "my-readonly-user";
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA reporting TO "my-readonly-user";

-- Grant access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "my-readonly-user";
ALTER DEFAULT PRIVILEGES IN SCHEMA reporting GRANT SELECT ON TABLES TO "my-readonly-user";

It will only apply to 1 single database (the current one). If I login with the readonly user and switch to some databases, I don't see any tables except in the first database.

A cluster holds many databases, which hold many schemas. Schemas (even with the same name) in different DBs are unrelated. Granting privileges for a schema only applies to this particular schema in the current DB (the current DB at the time of granting). https://stackoverflow.com/a/24923877/1409047

Because of that limitation, it seems to make the grant snippet from above more complex. Should I iterate all my databases somehow and run the snippet? How would I do the database switch? Is it even possible in plain SQL (as required by Vault's API)? Anyone did this before?

Note: doing the same in MySQL only requires 2 lines of code, using wildcards *.* which is not supported in Postgres afaik:

CREATE USER '{{name}}'@'10.0.0.0/255.0.0.0' IDENTIFIED BY '{{password}}';
GRANT SELECT, SHOW DATABASES, SHOW VIEW ON *.* TO '{{name}}'@'10.0.0.0/255.0.0.0';

score 6 · Accepted Answer · answered Nov 10 '20 at 02:18

It is a deliberate design decision that an SQL statement cannot affect objects in a database different from the one that you are connected to.

Yes, you will have to iterate through all databases in your cluster and run your script there.

Note that there is a mistake in your script: you shouldn't grant a read-only user USAGE on sequences, else they can modify the sequence value. SELECT is fine.

What I would do is create a read_only_group (with NOLOGIN) and grant all these permissions to that role. Then, when there is a request for a read-only user, create a user and add it to that group, so that it inherits the group privileges. Don't grant anything to the user itself, so that you can easily DROP it when it is no longer needed.

Thanks Laurenz! Very helpful. We use Flyway and built a python wrapper that iterates every database and applies our flyway migrations to them. We created a migration which provisions a read-only and read-write role for both schema's on the current connected database, and applied that migration to all databases. Then in Hashicorp Vault, we just create a user role and grant the read-only or read-write role to this temporary user. After a few hours the user gets dropped. — Jesse, Nov 16 '20 at 09:56

score 0 · Answer 2 · answered Mar 30 '21 at 10:47

If you want to grant readonly access to an user using only a Postgres script, you can do that:

CREATE EXTENSION IF NOT EXISTS dblink;

DO 
$$
DECLARE nome_banco TEXT;
DECLARE template_conexao TEXT;
DECLARE string_conexao TEXT;
DECLARE nome_usuario TEXT;
BEGIN
    template_conexao = 'user=foo password=bar dbname=';
    nome_usuario = 'baz';

    FOR nome_banco IN
        SELECT datname FROM pg_database
        WHERE datistemplate = false
    LOOP
        string_conexao = template_conexao || nome_banco;

        perform dblink_exec(string_conexao, 'GRANT CONNECT ON DATABASE "' || nome_banco || '" TO ' || nome_usuario);
        perform dblink_exec(string_conexao, 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO ' || nome_usuario);
    END LOOP;

END
$$

--DROP EXTENSION IF EXISTS dblink;

It connects to each database, and executes the script; it can be easily adapted for other situations where you need to execute a database-local command on all of the databases.

Remember that, for security purposes, after executing the script above, you should drop the dblink extension created by the script above, unless, of course, you are already using the extension for other purposes.

Create readonly user on all 1000 postgres databases and schema's

2 Answers2