DEV Community

leroykayanda
leroykayanda

Posted on • Edited on

Creating users in postgres

Creating a user

CREATE USER myusername WITH PASSWORD 'mypassword';

Assigning read only permissions

Check if a user has permissions on a table

SELECT * from information_schema.table_privileges WHERE grantee = 'read_only_user' and table_name = 'users';

Grant read only permissions

GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_user;

Whenever the user ted creates a table, it will grant select rights for read_only_user.

ALTER DEFAULT PRIVILEGES
FOR USER ted
IN SCHEMA public
GRANT SELECT ON TABLES TO read_only_user;

Assigning read and write permissions

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO tom;

ALTER DEFAULT PRIVILEGES
FOR USER admin
IN SCHEMA public
GRANT ALL PRIVILEGES ON TABLES TO tom;

Deleting a user

Assign the object ownership from the error detail to another user.

REASSIGN OWNED BY tom TO harry;

Remove the database object connections to the user with:

DROP OWNED BY tom;

At the moment, the user no longer has any dependencies. To drop the user, run

DROP USER tom;

Updating a user password

ALTER USER tom WITH PASSWORD 'fvfvfvafdfgff';

To give a user access to the postgres pg_stat_activity table, execute the command below from a superuser account.

GRANT pg_read_all_stats TO username;

You may need to kill a process like this.

SELECT pg_cancel_backend(31809);

You need to this permission.

GRANT pg_signal_backend TO brian;

Granting read permissions on sequences
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO myuser;

If you want to grant the user the ability to modify sequences directly (e.g., to reset them), you can include the UPDATE permission as well:

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

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more →

Top comments (0)

Image of Docusign

🛠️ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more