3

I want to grant read/write privileges to new user only to one database, so he can't access other databases.

After I created new user with: sudo -u postgres createuser <username> What privileges this user get?

Is this all I need: GRANT ALL PRIVILEGES ON my_db TO new_user; to get access to only one database?

What is the best way to do this?

Using PostgreSQL 10

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
volimfritule
  • 47
  • 1
  • 6
  • The user might need additional priviliges on the schema's and other objects in the database. Make sure you have revoked CONNECT privileges from PUBLIC for all the other databases otherwise the new user can connect to those. – Eelke Feb 08 '19 at 18:14
  • So new user gets privileges to (public)all databases and I need to revoke CONNECT from public. After that user has no rights to connect to any database? And then I can GRANT ALL PRIVILEGES on my db to user and thats it? :) – volimfritule Feb 08 '19 at 18:32

1 Answers1

0

By default, PUBLIC (everyone) is allowed to connect to all databases. So you'd have to revoke that privilege and hand out CONNECT more judiciously.

In addition to that, you'd have to make sure that every user has CREATE on all schemas in “his” database and the necessary privileges on all tables, because privileges on the database itself are not enough to access the objects in the database.

It could be the simplest solution to use REASSIGN OWNED to give the user ownership of all objects in “his” database.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263