Search code examples
postgresqlprivileges

postgres 9.6 set give user privileges to database

So this should be easy but I cannot figure this out. I am using postgres 9.6 I have a postgres user and a database. I want to give that user the ability to do ANYTHING on a given database only. I have tried the following commands which don't work: 

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;
ALTER DATABASE mydatabase OWNER TO myuser;

The documentation seems pretty clear that ALL PRIVILEGES should include update, insert etc, but then when I log in the user can't actually do anything. I have also seen examples such as :

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO user_name;

But im not sure how to restrict that to one database? Any help is greatly appreciates.

Solution

  • In PostgreSQL the only way to allow users access to all objects in a database with a single statement is to give them superuser privileges. But you should not do that.

    Proceed in stages:

    1. Access to the database:

      By default, everybody has access to all databases anyway, and you'd configure that in pg_hba.conf. If you have removed the permissions for PUBLIC on the database, use

      GRANT ALL ON DATABASE ... TO ...;

    2. Access to the schemas:

      For all schemas, run

      GRANT ALL ON SCHEMA ... TO ...;

    3. Access to the tables:

      For all schemas, run

      GRANT ALL ON ALL TABLES IN SCHEMA ... TO ...;

    4. Access to the sequences:

      For all schemas, tun

      GRANT ALL ON ALL SEQUENCES IN SCHEMA ... TO ...;

    5. Access to the functions:

      By default, functions are created with EXECUTE privileges for PUBLIC. If that has been removed, grant access with

      GRANT ALL ON ALL FUNCTIONS IN SCHEMA ... TO ...;

    There are other, less frequently used things like large objects or foreign servers. You'd have to grant privileges for those as well. Look at the documentation for GRANT for the details.