How to make a new user and give them perms to a db in postgres

postgres-new-user.md

PostgreSQL New User Guide

This assumes the new user is kierank and the database is hackatime

Guide

1. Generate a Random 16-Character Password

To generate a strong 16-character password, you can use the following command:

openssl rand -base64 16

This will produce a random password. For example:

q9Km7Txp4zVRh2Rg

2. Login to PostgreSQL

Log in to your PostgreSQL instance using a superuser (e.g., postgres) account:

su postgres
psql

3. Make User

Execute the following replacing kierank and pass with your user and password generated above

CREATE USER kierank WITH PASSWORD 'pass';

4. Grant Access to the Database

Grant the user kierank access to connect to the hackatime database:

GRANT CONNECT ON DATABASE hackatime TO kierank;

5. Grant Schema Access

Switch to the hackatime database:

\c hackatime

Grant the user kierank usage rights on the schema (usually public unless a different schema is used):

GRANT USAGE ON SCHEMA public TO kierank;

6. Grant Permissions on Existing Tables

Grant kierank permissions for all existing tables in the public schema:

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

7. Grant Permissions on Existing Sequences

If sequences are used for auto-increment fields (e.g., SERIAL), grant kierank the necessary permissions:

GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO kierank;

8. Set Default Privileges for Future Objects

To ensure kierank automatically gets the appropriate permissions on any newly created tables or sequences in the future, run these commands:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO kierank;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO kierank;

9. Verify Permissions

To verify the permissions, you can use the following query to list the privileges granted to kierank:

\dp

This will display the access privileges for all tables, sequences, and views in the public schema.

---

10. Update or Modify Permissions (If Needed)

If you need to update or revoke permissions in the future, you can use commands like:

• Revoke specific permissions:

  REVOKE SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM kierank;

• Grant additional permissions:

  GRANT TRUNCATE ON ALL TABLES IN SCHEMA public TO kierank;

---

Summary of Key Permissions:

• Tables (arwd):

  • a: INSERT
  • r: SELECT
  • w: UPDATE
  • d: DELETE

• Sequences (rwU):

  • r: USAGE (view current value)
  • w: UPDATE (modify value)
  • U: USAGE (set the value)