CODEWITHSUNDEEP
postgresqldockerprivileges
Anton Tretyakov
Anton Tretyakov

Reputation: 355

postgres -- default privilidges are not set when initializing db in docker

This is not a duplicate of this question as the one 1) does not seem to have an accepted answer and 2) the proposed answer neither alleviates my problem (as I implemented the advise contained there but the problem remained) nor seem to have helped the OP of the mentioned question anyways.

I am trying to run a postgres docker container. Before container startup the initialization script is run with the sole purpose of creating a db and a couple of users with predefined privileges -- in this example admin ought to have all the necessary privileges and reader, well, is bound to only SELECT stuff. The tables inside the db are created by admin from other container.

All's good, but the problem is that reader can not only SELECT but do other stuff like INSERT.

In the below init script written (presumably) according to the docs I initially tried to create users without intermingling roles -- but that blocker any access for reader at all. Then I tried to put the reader in the role of admin and limit unnecessary privileges, but the latter didn't go. The latest revision of the script I show below.

#!/bin/bash

set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE DATABASE db;
    REVOKE CONNECT ON DATABASE db FROM PUBLIC;

    CREATE USER admin;
    ALTER USER admin PASSWORD '$ADMIN_PASS';
    GRANT ALL PRIVILEGES ON DATABASE db TO admin;
    ALTER DEFAULT PRIVILEGES 
        GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO admin;

    CREATE USER reader IN ROLE admin;
    ALTER USER reader PASSWORD '$READER_PASS';
    REVOKE ALL PRIVILEGES ON DATABASE db FROM reader;
    ALTER DEFAULT PRIVILEGES 
        REVOKE ALL ON TABLES FROM reader;
    GRANT CONNECT ON DATABASE db TO reader;
    ALTER DEFAULT PRIVILEGES 
        GRANT SELECT ON TABLES TO reader;
EOSQL

EDIT: The last edition of the script is the following, accounting for comments below, but the problem persist.

#!/bin/bash

set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE DATABASE db;

    CREATE USER admin;
    ALTER USER admin PASSWORD '$ADMIN_PASS';

    CREATE USER reader IN ROLE admin;
    ALTER USER reader PASSWORD '$READER_PASS';
EOSQL

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname db <<-EOSQL
    REVOKE CONNECT ON DATABASE db FROM PUBLIC;

    GRANT ALL PRIVILEGES ON DATABASE db TO admin;
    ALTER DEFAULT PRIVILEGES 
        FOR ROLE admin
        GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO admin;

    REVOKE ALL PRIVILEGES ON DATABASE db FROM reader;
    ALTER DEFAULT PRIVILEGES 
        FOR ROLE admin
        REVOKE ALL ON TABLES FROM reader;
    GRANT CONNECT ON DATABASE db TO reader;
    ALTER DEFAULT PRIVILEGES 
        FOR ROLE admin
        GRANT SELECT ON TABLES TO reader;
EOSQL

Upvotes: 2

Views: 124

Answers (1)

akop
akop

Reputation: 7871

I had the same problem. The script will be executed by the postgres-user and as mentioned in the referenced question (Postgres readonly user cannot access tables), the ALTER DEFAULT PRIVILEGES ... will only count for the executing user.

My fix for that something is like that:

echo "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reader;" | psql -d "postgres" -U admin

Upvotes: 0

Related Questions