CODEWITHSUNDEEP
postgresql
André Diego Piske
André Diego Piske

Reputation: 196

Why does postgres seem to have lax default permissions?

I'm trying to create a database and a user with limited privileges. The user should have access only to that database, nothing more.

In a blank slate Postgres 13 deployment using Docker, I connect with the user postgres, a superadmin, and run the following:

CREATE DATABASE db_foo;
CREATE USER usr_bar with NOINHERIT LOGIN password 'pwd1234';

That's just it. Nothing more than that. Then I connect to it with the newly created user, using psql -h <pg_host> -U usr_bar -d <db_name>.

Replacing <pg_host> with either 127.0.0.1 when running psql from the Docker host machine or with the docker container name when running psql from another docker container. Also replacing <db_name> with either postgres or db_foo; they both yield the same odd behavior.

What I expected to happen is that the login above (with usr_bar user) for any of the databases would fail due to lack of permission. Or that at least I wouldn't be able to make any changes, but I'm able to run, for instance, a create table command and it works. I would expect the user to not have any permissions by default, since no GRANT was performed.

So my question is: Why does this newly created user has so much permission by default? What am I doing wrong? If anyone can also suggest how to solve this, you're welcome; but I'd like to understand the reasoning behind it.

NOTE: For the docker image, I tried with two different ones, but had the same results. They are:

$ docker run --rm -ti -e POSTGRES_PASSWORD=root1337 --network some_net --name some-pg postgres:13

and

$ docker run --rm -ti -e POSTGRESQL_PASSWORD=root1337 --network some_net --name some-pg bitnami/postgresql:13

Upvotes: 1

Views: 176

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246798

You are not doing anything wrong. There are two things that conspire to produce the behavior you see:

  1. The default permissions for databases allow CONNECT and TEMP to PUBLIC, i.e., everyone.

    That may seem lax, but it is mitigated by the fact that the default pg_hba.conf does not allow remote connections at all, which is restrictive.

    In a way, the CONNECT permission on databases and pg_hba.conf rules overlap: they both restrict access of users to databases. I guess it was decided that being strict in one of them is good enough.

  2. The default permissions on the public schema allow CREATE to PUBLIC.

    That is an unsafe default, and the documentation recommends to REVOKE that privilege in databases.

    The reason why this is not changed is backward compatibility, which is highly priced in PostgreSQL.

Upvotes: 3

Related Questions