intricatecloud

managing db users in postgres & mysql

A guide for when you have to add/remove/update/and/20/other/things in both mysql & postgres databases, and you wind up getting lost trying to do seemingly simple things. I've been keeping a running doc where I keep snippets of SQL for those times when I have to remember, and I hope they help if you happen to be using both postgres and mysql and need to manage user access.

Below, we've got 10 frequently used SQL snippets for managing users.

  1. add a user
  2. reset a password
  3. checking users privileges
  4. revoking privileges
  5. remove a user
  6. expire passwords/temporary credentials
  7. checking expiration time
  8. granting read-only access
  9. checking if a user exists
  10. some debug information that might help

If you want to play around with these scripts with a development database, start them up using docker and connect to it:

in postgres:

# start postgres in docker
docker run --rm --name pgsql -e POSTGRES_PASSWORD=password -p 5555:5432 -d postgres
e0e76a6af8b1f83cf91295ae5430cbf2ecd95337977b53653f312024d8aab3ad

# connect to postgres
psql -h 127.0.0.1 -p 5555 -U postgres -d postgres

in mysql:

# start mysql in docker
docker run --rm --name mysql -p3307:3306 -e MYSQL_ROOT_PASSWORD=password -d mysql
# connect to mysql
mysql -h 127.0.0.1 -P 3307 -u root -p

1. add a user

in postgres:

CREATE USER '${user}' WITH PASSWORD '${password}';
GRANT ALL ON DATABASE '${db}' TO '${user}'; -- grants read/write to everything in this database instance
-- OR
GRANT CONNECT ON DATABASE '${db}' to '${user}'; -- only allows the user to connect, but nothing more.

* https://www.postgresql.org/docs/8.0/static/sql-createuser.html

in mysql:

CREATE USER '${user}' IDENTIFIED BY '${password}';
GRANT ALL PRIVILEGES ON ${db}.* TO '${user}';

* https://dev.mysql.com/doc/refman/8.0/en/adding-users.html

2. reset a password

in postgres:

ALTER ROLE '${user}' WITH PASSWORD '${pw}';

* https://www.postgresql.org/docs/8.0/static/sql-alteruser.html

in mysql:

ALTER USER '${user}' IDENTIFIED BY '${pw}'

*heres 2 more ways of doing this: https://www.geeksforgeeks.org/mysql-change-user-password/

3. checking a users privileges

in postgres:

This will list all the databases and show you all the roles that have access to it.

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres

This will show all the role names and the other roles they include.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

This will show all the tables that a user has access to

postgres=# \z
                                 Access privileges
 Schema |  Name  | Type  |     Access privileges     | Column privileges | Policies
--------+--------+-------+---------------------------+-------------------+----------
 public | foobar | table | postgres=arwdDxt/postgres+|                   |
        |        |       | dperez=r/postgres         |                   |
 public | test   | table | postgres=arwdDxt/postgres+|                   |
        |        |       | dperez=r/postgres         |                   |
(2 rows)
 ...

See the postgres docs on grants to decipher that description

in mysql:

mysql> SHOW GRANTS FOR '${user}';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO '${user}'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+

4. revoke privileges

in postgres:

REVOKE ALL PRIVILEGES ON DATABASE '${db}' from '${user}';

* https://www.postgresql.org/docs/9.1/static/sql-revoke.html

in mysql:

REVOKE ALL PRIVILEGES ON '${db}.*' FROM '${user}';

*https://dev.mysql.com/doc/refman/8.0/en/revoke.html

5. remove a user

in postgres:

DROP ROLE IF EXISTS '${user}';

You might see an error message like:
ERROR: role "testuser" cannot be dropped because some objects depend on it DETAIL: $somethingUseful
this means that your user has dependencies with other objects - things like privileges and other tables. In this case, you'd need to run:

REVOKE ALL PRIVILEGES ON DATABASE '${db}' from '${user}';
DROP ROLE IF EXISTS '${user}';

If this fails, then try

DROP OWNED BY '${user}';
DROP ROLE IF EXISTS '${user}'

* https://stackoverflow.com/questions/3023583/postgresql-how-to-quickly-drop-a-user-with-existing-privileges

The irony here is that you would typically use DROP ROLE IF EXISTS so that a script would not error out if the role did not exist. However, when the role does NOT exist, you get back a successful response. When the role DOES exist, it will likely have privileges and require you to run REVOKE ALL PRIVILEGES - and unfortunately you cannot do REVOKE ALL ... IF EXISTS. It kind of renders the whole DROP ROLE IF EXISTS convenience statement useless.

in mysql:

DROP USER IF EXISTS '${user}';

Thank you for being so straightforward about it, mysql.

6. expire a password / temporary credentials

in postgres:

ALTER ROLE '${user}' WITH PASSWORD '${password}' VALID UNTIL '${expiration_timestamp}';

Here, expiration_timestamp has the format Nov 3 12:00:00 2018 +1 and means the time at which the password will no longer work.
*https://www.postgresql.org/docs/9.2/static/sql-alterrole.html

in mysql:

ALTER USER '${user}' PASSWORD EXPIRE INTERVAL 1 DAY;

The statement seems to only allow DAY as the interval unit. So INTERVAL 90 DAY would be correct (note: no plural s in day).

7. how to check expiration time of password

Once you set a password expiration time, it would be useful to see what that time is.

in postgres:

SELECT valuntil AS valid_until FROM pg_user WHERE usename = '${user}';

in mysql:

SELECT DATE_ADD(password_last_changed, interval password_lifetime day) AS valid_until FROM mysql.user WHERE user = '${user}';

8. granting read-only access

in postgres:

GRANT USAGE ON SCHEMA public TO '${user}';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO '${user}';

* related thread about what grant all privileges on database actually does
You might find later on that if you add a table, this read-only user does not have access to read that table. In which case, run the following to make sure that you by default get privileges on tables:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO '${user}';

* more info here - https://stackoverflow.com/questions/760210/how-do-you-create-a-read-only-user-in-postgresql

in mysql:

GRANT SELECT ON '${db}'.* TO '${user}';

9. check if a user exists

in postgres:

SELECT 1 FROM pg_roles WHERE rolname='${user}';

in mysql:

SELECT EXISTS(SELECT 1 FROM mysql.user WHERE user = '${user}');

10. get some debug info about your current user

in postgres:

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "127.0.0.1" at port "5555".
postgres=# SELECT CURRENT_USER;
 current_user
--------------
 postgres
(1 row)

postgres=# SELECT CURRENT_USER(); -- you can't use it as a function
ERROR:  syntax error at or near "("
LINE 1: SELECT CURRENT_USER();

in mysql:

mysql> select CURRENT_USER();
mysql> select CURRENT_USER; -- both this and the above function return the same result
+----------------+
| current_user() |
+----------------+
| dperez@%       |
+----------------+
mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.22-22, for osx10.11 (x86_64) using  EditLine wrapper

Connection id:    683
Current database: dpereztest
Current user:   dperez@10.162.9.100
SSL:      Cipher in use is DHE-RSA-AES128-SHA
Current pager:    less
Using outfile:    ''
Using delimiter:  ;
Server version:   8.0.11 Source distribution
Protocol version: 10
Connection:   127.0.0.1 via TCP/IP
Server characterset:  utf8mb4
Db     characterset:  utf8mb4
Client characterset:  utf8
Conn.  characterset:  utf8
TCP port:   5555
Uptime:     2 days 7 hours 55 min 53 sec

Threads: 3  Questions: 107602  Slow queries: 0  Opens: 285  Flush tables: 2  Open tables: 259  Queries per second avg: 0.534
--------------

I hope this helps as a useful guide the next time you need to manage users on a postgres or mysql database. There's dozens of more things you can do to customize your users and their access levels, but this guide should serve as a starting off point with links to the docs to help you do what you're trying to do.

If you're managing users and credentials across multiple teams, you might want to check out:

Tags:
Danny Aslam-Perez
Sr Software Developer and Engineering Manager at an edtech company writing about leading teams, automated testing, and building sites hosted on AWS.

You might be interested in…

108 Comments. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu