Why can a new user select from any table?

Question

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?

Answer 1

To address the question of why test can SELECT from tables without a GRANT, it is because you have not explicitly run REVOKE for the ability to connect to your database(s) from PUBLIC, and then explicitly run GRANT for your test user:

REVOKE connect ON DATABASE testdb FROM PUBLIC;
GRANT connect ON DATABASE testdb TO test;

Once that is done, you would REVOKE SELECT for your user within that database your tables and other items you wish to prevent the user from accessing, and then explicitly GRANT SELECT for the views.

Note that user here could also be role, which multiple users could then be added to.

Answer 2

^{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.