in Databases

Granting permissions in Postgres compared to MySQL

It is always advised to restrict permissions for connections to the minimal amount that they need to perform their function. When it comes to a database you have a few permissions to consider. In the blog post here, I go over how to restrict what hosts or ip’s can log into a system, but it is important to also restrict what users can do once they connect to the system. If a user only needs select permission, then there is no need to give them insert, update, and delete privileges as well. If they need all of the Data Manipulation Langauge (DML) permissions they still may not need Data Definition Language (DDL) permissions too. I’d like to go over what setting up these user level permissions looks like in MySQL and what I have discovered about setting these permissions in Postgres.

Creating a user and granting permissions to the database

For both MySQL and Postgres, I will assume you are in their native cli for this. To enter the mysql cli, type ‘mysql’. To enter postgres, type ‘psql’.

Mysql

First we create the user


create user [username]@'[ip]' identified by '[password'];

#alternatively we can use “identified by password ‘[passwordhash]’;” to specify the encrypted password

Next we grant permissions to the database


grant select, insert, update, delete on [databasename].* to [username]@'[ip]'; 

If we wanted individual tables to be specified for certain permissions, they could be granted using databasename.tablename where databasename.* is in use above.

Postgres

First we create the user and give them the ability to login to the server


create user [username] with login unencrypted password '[password]';

#alternatively you could get a password hash and use that with the keyword encrypted

You then must use the context of the database that you want to add them to


\c [databasename]

Then you can grant them usage to the schema


grant usage on schema public to [username];

Then you can grant them the individual permissions you want them to have


grant select, insert, update, delete on all tables in schema public to [username];

That statement only gives the ability to use those permissions on existing tables though. You also have to adjust the default privileges for the database so that any tables created in the future will have the same access for your user. Applying the default permissions now will prevent maintenance requiring you to come back and add permissions again in the future.


alter default privileges in schema public grant select, insert, update, delete on tables to [username];

This still doesn’t apply to sequences which will need to have access to be updated


grant usage on all sequences in schema public to [username];

And then the default permissions for sequences…


alter default privileges in schema public grant usage on sequences to [username];

This still doesn’t apply to function though, which must be done separately


grant execute on all functions in schema public to [username];

But you guessed it, that didn’t apply to things created in the future, so we have to alter the default privileges for that too


alter default privileges in schema public grant execute on functions to [username];

Now you are possibly done. Depending on how your table was created, you may have inherited the ability to create tables even though we never explicitly gave it to our new user. There is a template0 database and a template1 database that are part of postgres. When you create a new database, you are actually copying a skeleton of one of these two databases and then adding on the things you specified in your create statement. By default, the template1 database is used. The thought is that you can tune the template1 database to have various default things in it, and the template0 database is the minimal database to allow things to work. If your database was created with the “TEMPLATE template0” option at the end of it, you are probably good to go. If not, you will have to remove the create table permissions.

To verify if this permission exists, run the below query. If you get a row back, then the permission exists for the username you checked.


select 1 where (select has_schema_privilege('[username]','public','CREATE')) = 't';

To remove the permission, run the below command


revoke create on database [databasename] from [username];

Summary

In short, dealing with permissions in Postgres has been a much more involved process than what I am used to in MySQL. If there are any Postgres users willing to comment with an easier way or explanation as to why it is so hard to restrict a user to minimal permissions, I would like to get a better understanding of the thought process, or if there is something I have missed in my research on how to properly add an application user.

Write a Comment

Comment

  1. Did you mean that AFTER we create new tables, we have to run the following command so that [username] may have access to the new tables?

    alter default privileges in schema public grant select, insert, update, delete on tables to [username];

    Or does running that command automatically grant access to all tables created in the future?

    This may be a dumb question, but I wish you were more explicit about it. The command itself suggests the former to me.

    I am using this for a Business Intelligence app that also connects to the DB.

    • Thanks for the feedback. I will add some verbiage to describe when to apply the permissions to remove the ambiguity.

      When you run a grant against the schema it only applies to the things that exist in the schema at the time you run the grant. The default permissions line applies the grants specified automatically to tables that are created in the future. I have been running all of the grants listed above at the time that I setup the user.

      If you did not alter the default permissions and created new tables, you would likely have to grant permission to those new tables explicitly. Altering the default permissions should help prevent you from the need to come back and manage permissions for new tables that are added after the user is setup.

      It is also important to note as recently discovered by our team that the permissions are only applicable for the role from which they are granted. For us, we deploy all of our tables from a dedicated deployment user. We recently experimented with a product that interacts with the database that had a need to create additional tables of its own. These new tables were not accessible from our application users even though we had specified “alter default permissions” on the schema. In digging into it, the tables were owned by the other non-deployment user and we found we had to apply permissions to the schema either from that user, or as a superuser with the below lines.


      #apply grants to all existing tables for your appuser (you can also specify just the new tables if you'd like)
      GRANT select, insert, update, delete ON ALL TABLES IN SCHEMA public TO "appuser";
      GRANT execute ON ALL functions IN SCHEMA public TO "appuser";
      GRANT USAGE ON ALL sequences IN SCHEMA public TO "appuser";

      #apply permissions for your app user to access things created by the admin user going forward
      ALTER DEFAULT PRIVILEGES FOR ROLE admin IN SCHEMA public GRANT INSERT, UPDATE, DELETE, SELECT ON TABLES TO appuser;
      ALTER DEFAULT PRIVILEGES FOR ROLE admin IN SCHEMA public GRANT execute ON functions TO appuser;
      ALTER DEFAULT PRIVILEGES FOR ROLE admin IN SCHEMA public GRANT usage ON sequences TO appuser;

      You can also check the default permissions on the schema with the below command:
      \ddp

      • Thank you so much, this was really useful for me as a person who had to switch from MySQL to Postgres.

        In my case, `FOR ROLE admin` was the key moment. I kept trying `ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT INSERT, UPDATE, DELETE, SELECT ON TABLES TO appuser` and didn’t have a clue why appuser still does not have access to tables which where being created later by my Laravel migrations using a dedicated connection with admin user.
        As soon as I added `FOR ROLE admin`, it finally started to work as expected.

        Postgres roles and rules are so tricky and unintuitive sometimes…

        Thanks again, I have bookmarked this article and will share it with my colleagues.

  2. Sir,
    Please see below manual steps to grant user privileges for a newly added user in MySQL 5.7 database

    GRANT USAGE ON *.* TO ‘ABC123’@’%’ ;
    GRANT SELECT ON `%`.* TO ‘ABC123’@’%’ ;

    I’m trying to put the above 2 GRANT statements in a stored procedure and call the procedure by passing the parameter. Somehow I’m getting an error while creating the procedure using the wild cards (*, %)
    Trying best to understand if we can remove the wild cards and replace it with database name directly to avoid syntax error.
    See below definition of the procedure.

    DROP PROCEDURE IF EXISTS ID_LINKER_GRANT_Proc$$
    CREATE PROCEDURE ID_LINKER_GRANT_Proc(IN eid VARCHAR(6))
    BEGIN
    GRANT USAGE ON *.* TO eid@’%’;
    GRANT SELECT ON `%`.* TO eid@’%’;

    END$$
    DELIMITER ;

    Basically, i’m trying to grant (SELECT, UPDATE, DELETE) privileges to the DB tables depending upon the user role. Unfortunately, MySQL 5.7 do not support creation of ROLES but this feature is available from MySQL 7.0 onwards.
    For now, I’m exploring option to create stored procedures and execute grant privileges if possible otherwise will pursue route to upgrade database to higher version to create roles (may consider this approach later).

    Any help fixing the syntax of above procedure or recommending a better approach to resolve this issue is highly appreciated – thanks!

    • I don’t have a MySQL dev box handy at the moment to tinker with your syntax, but what I did in the past to get around the lack of role’s in MySQL was utilize an open source bash script that copies one user and their permissions to a new user. This allowed us to setup a template user for dev or application access that could be rolled out to various usernames that were then actually used. Check out https://github.com/wstrucke/sysadmin/blob/master/mysql-copy-user.sh

      It’s coming up on 3 years since I’ve used MySQL for any projects as the place I work now is mostly Postgres. I’m excited to hear that MySQL is introducing roles starting in version 7.

      One thing you may try from a syntax perspective is stitching together your grant statements into a variable and then exec the variable. It could be something goofy where inside the proc it isn’t interpolating the variable the way you would expect. That would also allow you to treat it as an interpolated string and put the single ticks around the username variable as well like what you have in the first section of your comment.

      Regarding your question about replacing wildcards with the database name, you should be able to do that for at least the first asterisk. Here is the example of things you can put in that section from the docs. I normally used it for [dbname].* or [dbname].[table-name]


      priv_level: {
      *
      | *.*
      | db_name.*
      | db_name.tbl_name
      | tbl_name
      | db_name.routine_name
      }