Postgres handy commands

1
2
3
4
5

-- With login --
CREATE USER my_pg_user WITH PASSWORD 'your_secure_password';

-- No login --
CREATE ROLE my_pg_user WITH LOGIN PASSWORD 'strong_password';

1
2
3
4
5

-- Update password for a specific user
ALTER USER 'my_pg_user'@'localhost' IDENTIFIED BY 'new_secure_password';

-- If you are updating your OWN password
SET PASSWORD = PASSWORD('new_secure_password');

1

DROP USER IF EXISTS 'my_pg_user'@'localhost';

1
2
3

GRANT CONNECT ON DATABASE my_database TO my_pg_user;
GRANT USAGE ON SCHEMA my_database TO my_pg_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA my_database TO my_pg_user;

1

REVOKE CONNECT ON DATABASE my_database FROM PUBLIC;

1
2
3

ALTER DEFAULT PRIVILEGES IN SCHEMA my_database;
GRANT ALL PRIVILEGES ON TABLES my_database TO my_pg_user;
GRANT SELECT ON TABLE customers, orders TO my_pg_user;