Search code examples
postgresqlprivileges

Grant default privileges for specific database in Postgres

I have a database called funnycode. I also have a user called funnycode_user.

How do I grant default privileges to funnycode_user so that the user can:

  1. Create a database under the name funnycode.
  2. User can connect to any future database with the name funnycode, but not other databases.
  3. User can select from all tables and has access to all sequences on any future database under the name funnycode but not other databases.

Is this possible in Postgres? If so, how? I read the documentation here, but I don't see how this:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT ON TABLES TO funnycode_user;

Is tied to a specific database, in my case that would be the funnycode database?

Solution

  • The user that creates a database automatically owns that database (unless a different owner is specified) and thus automatically has all privileges on the database. You don't need any additional grants to enable that user creating and using tables in the database.

    So if you give funnycode_user the create database privilege, he can create any database he wants, not just funnycode. There is no way to prevent that.

    Every new database that is created automatically grants the connect privilege to the role public. If you don't want funnycode_user to connect to other database, you need to revoke that privilege from the public role for every database.

    Another way of limiting access to a specific database for a specific user, is through pg_hba.conf

    You would need an entry like that: 

    # TYPE  DATABASE        USER              ADDRESS        METHOD
host    funnycode       funnycode_user    0.0.0.0/0      md5

    The entry 0.0.0.0/0 means that funnycode_user can connect from any IP address. Even if funnycode_user created other databases, he wouldn't be able to connect to them.

    I think the better (cleaner) way is to not give that user the privilege to create database. Just create that database once, make funnycode_user the owner and that's it.

    E.g. as the superuser run:

    create user funnycode_user password 'VerySecret';
create database funnycode owner = funnycode_user;

    You only need to do that once (or after funnycode_user decides to drop the database) and you don't need to give funnycode_user the privilege to create databases.