PostgreSQL Permissions – Why Can a New User Select from Any Table

access-controlpermissionspostgresqlpostgresql-9.1

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?

Best Answer

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.

Related Solutions

PostgreSQL – Created User Can Access All Databases Without Grants

At the SQL level, every user can indeed connect to a newly created database, until the following SQL command is issued:

REVOKE connect ON DATABASE database_name FROM PUBLIC;

Once done, each user or role that should be able to connect has to be granted explicitly the connect privilege:

GRANT connect ON DATABASE database_name TO rolename;

Edit: In a multi-tenant scenario, more than just the connect privilege would be removed. For multi-tenancy tips and best practices, you may want to read on the postgresql public wiki: Shared Database Hosting and Managing rights in PostgreSQL.

PostgreSQL – Why New User Can Create Table

When you create a new database, any role is allowed to create objects in the public schema. To remove this possibility, you may issue immediately after the database creation:

REVOKE ALL ON schema public FROM public;

Edit: after the above command, only a superuser may create new objects inside the public schema, which is not practical. Assuming a non-superuser foo_user should be granted this privilege, this should be done with:

GRANT ALL ON schema public TO foo_user;

To know what ALL means for a schema, we must refer to GRANT in the doc, (in PG 9.2 there are no less than 14 forms of GRANT statements that apply to different things...). It appears that for a schema it means CREATE and USAGE.

On the other hand, GRANT ALL PRIVILEGES ON DATABASE... will grant CONNECT and CREATE and TEMP, but CREATE in this context relates to schemas, not permanent tables.

Regarding this error: ERROR: no schema has been selected to create in, it happens when trying to create an object without schema qualification (as in create table foo(...)) while lacking the permission to create it in any schema of the search_path.

Best Answer

Related Solutions

PostgreSQL – Created User Can Access All Databases Without Grants

PostgreSQL – Why New User Can Create Table

Related Question