DEV Community

Danny Perez
Danny Perez

Posted on • Originally published at intricatecloud.io

managing db users in postgres & mysql

#database #sql #devops

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

# connect to postgres
psql -h 127.0.0.1 -p 5555 -U postgres -d postgres
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

* 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}';
Enter fullscreen mode Exit fullscreen mode

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

2. reset a password

in postgres:

ALTER ROLE '${user}' WITH PASSWORD '${pw}';
Enter fullscreen mode Exit fullscreen mode

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

in mysql:

ALTER USER '${user}' IDENTIFIED BY '${pw}'
Enter fullscreen mode Exit fullscreen mode

*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
Enter fullscreen mode Exit fullscreen mode

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 | {}
Enter fullscreen mode Exit fullscreen mode

This will show all the tables that a user has access to (thanks for the tip, @dmfay )

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)
 ...
Enter fullscreen mode Exit fullscreen mode

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 |
+---------------------------------------------------------------------+
Enter fullscreen mode Exit fullscreen mode

4. revoke privileges

in postgres:

REVOKE ALL PRIVILEGES ON DATABASE '${db}' from '${user}';
Enter fullscreen mode Exit fullscreen mode

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

in mysql:

REVOKE ALL PRIVILEGES ON '${db}.*' FROM '${user}';
Enter fullscreen mode Exit fullscreen mode

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

5. remove a user

in postgres:

DROP ROLE IF EXISTS '${user}';
Enter fullscreen mode Exit fullscreen mode

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}';
Enter fullscreen mode Exit fullscreen mode

If this fails, then try

-- another_user could == 'root' (or an admin account)
REASSIGN OWNED BY '${user}' TO '${another_user}'; 
DROP ROLE IF EXISTS '${user}';
Enter fullscreen mode Exit fullscreen mode

* 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}';
Enter fullscreen mode Exit fullscreen mode

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}';
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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}';
Enter fullscreen mode Exit fullscreen mode

in mysql:

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

8. granting read-only access

in postgres:

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

* 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}';
Enter fullscreen mode Exit fullscreen mode

* 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}';
Enter fullscreen mode Exit fullscreen mode

9. check if a user exists

in postgres:

SELECT 1 FROM pg_roles WHERE rolname='${user}';
Enter fullscreen mode Exit fullscreen mode

in mysql:

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

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();
Enter fullscreen mode Exit fullscreen mode

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
-------------------
Enter fullscreen mode Exit fullscreen mode

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:

Interested in seeing more articles like this? Follow me here on dev.to OR subscribe to my list to get notified when I've got new posts.

Top comments (2)

Collapse
 
dmfay profile image
Dian Fay •

A couple of Postgres points:

3. Checking privileges

psql's \z is a convenient shorthand for listing database object permissions.

5. DROP USER

It sounds like you're looking for REASSIGN OWNED BY $user-to-delete TO $another-user; which will handle any dependencies for you and give you a clear path to dropping the user. Don't just drop owned objects unless you're in a sandbox -- it's hard to get those back!

Collapse
 
intricatecloud profile image
Danny Perez •

I've updated the post - thanks for the feedback!

Read next

clericcoder profile image

Mastering Trace Analysis with Span Links using openTelemetry and Signoz (A Practical Guide,Part 2)

Abdulsalaam Noibi -

clericcoder profile image

Mastering Trace Analysis with Span Links using OpenTelemetry and Signoz (A Practical Guide, Part 1)

Abdulsalaam Noibi -

abdulkadir_saloum_770fee4 profile image

Exploring InstantDB: A New Approach to Local-First Databases

Abdulkadir Saloum -

tomjohnson3 profile image

Application Dependency Mapping: A Real-World Example

Thomas Johnson -