Why can a new user select from any table?

When I create a user in Postgres,

create user test with password 'test';

The user ends up being able to connect to every database in the instance. The user can also select from any table:

# psql -h localhost testdb test
Password for user test: *****
psql (8.4.20, server 9.1.13)
WARNING: psql version 8.4, server version 9.1.
         Some psql features might not work.
Type "help" for help.

testdb=> select * from testtable;
 id
------------
      1
(1 row)

The \l database listing shows that testdb has empty Access privileges. So why can a newly created user connect to that database?

The newly created "test" user can also select data from tables. Why can he do so without a grant of select privileges?

I'd like to create a user that can only connect to one database and only read from certain views. How can I prevent a user from reading directly from tables?

Also addressing the question in the comments.

Role public

Per documentation:


The key word PUBLIC indicates that the privileges are to be granted
to all roles, including those that might be created later. PUBLIC
can be thought of as an implicitly defined group that always includes
all roles.

Bold emphasis mine. Membership in public cannot be revoked (or granted). You can only revoke privileges from public. In your case, to block out all roles without explicit privileges, like @Robert already provided:

REVOKE ALL ON DATABASE testdb FROM PUBLIC;

Role postgres

The database role postgres is a superuser by default. You can take that away from postgres - but that would be unwise: everybody (including some client programs) expects postgres to be a superuser. You can also create more superusers (careful with that!). Superusers don't need privileges. The manual once more:


It should be noted that database superusers can access all objects
regardless of object privilege settings. This is comparable to the
rights of root in a Unix system. As with root, it's unwise to operate
as a superuser except when absolutely necessary.

As to your question:


Is it true that if \l or \dn+ displays empty access privileges, that
is the same as postgres=UC/postgres, =UC/postgres

Not exactly.

-
Privileges are granted by the owner or a superuser (or a role that was granted the privilege to do so). postgres=UC/postgres would mean postgres granted it, which is not accurate for empty privileges, but the effect is the same in a default installation.

-
However, public gets no privileges for new schemas and only CONNECT and TEMP (can create temporary tables) privileges for databases by default, not the CREATE privilege (cannot create schemas). That would be something like =UT/?? according to the list of possible privileges - the manual once more:

r -- SELECT ("read")
        w -- UPDATE ("write")
        a -- INSERT ("append")
        d -- DELETE
        D -- TRUNCATE
        x -- REFERENCES
        t -- TRIGGER
        X -- EXECUTE
        U -- USAGE
        C -- CREATE
        c -- CONNECT
        T -- TEMPORARY
  arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
        * -- grant option for preceding privilege

You can change default privileges. I wrote more about that in the related answer mentioned in the comments:

• Grant privileges for a particular database in PostgreSQL

But those default privileges only apply objects created after defaults were changed. So what does an empty ACL entry mean? The manual on the system catalog pg_default_acl:


Note that when an ACL entry in another catalog is null, it is taken to
represent the hard-wired default privileges for its object, not
whatever might be in pg_default_acl at the moment.

Bold emphasis mine.

StackExchange Database Administrators Q#73010, answer score: 3