How to create user in PostgreSQL | How to create new database user account
In Postgre SQL CREATE USER command used to create new database user account. To use the CREATE USER command the you must be a database superuser with all privilege. CREATE USER command can be used using the PSQL and PgAdmin tool.
Postgres Create user with password in post gre sql using PSQL syntax
CREATE USER username WITH ENCRYPTED PASSWORD 'password';
Postgres Create user with password in postgresql using PSQL example
CREATE USER novice WITH ENCRYPTED PASSWORD 'novice';
After successful creation of new user with password using the PSQL, the message will be displayed like above.
Postgres Create user with password in postgresql using PgAdmin query tool syntax
CREATE USER username WITH ENCRYPTED PASSWORD 'password';
Postgres Create user with password in post gre sql using PgAdmin query tool example
CREATE USER novice1 WITH ENCRYPTED PASSWORD 'novice1';
After successful creation of new user with password using the PgAdmin, the message will be displayed like above. You can see the newly created users under the Login/Group Roles section in PgAdmin.
PostgreSQL - Grant all privileges postgres | Postgres grant all privileges on database to user?
We can create database with the name novicetechie and create a user with the name novice to check the below mentioned postgresql grant permission sql queries. For create database example click here. For creating user example check above.
Grant CONNECT privileges to the database syntax
GRANT CONNECT ON DATABASE database_name TO username;Grant CONNECT privileges to the database example
GRANT CONNECT ON DATABASE novicetechie TO novice;Grant USAGE on schema syntax
GRANT USAGE ON SCHEMA schema_name TO username;Grant USAGE on schema example
GRANT USAGE ON SCHEMA public TO novice;Grant on all tables for DML statements: SELECT, INSERT, UPDATE, DELETE syntax
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema_name TO username;Grant on all tables for DML statements: SELECT, INSERT, UPDATE, DELETE example
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO novice;Grant all privileges on all tables in the schema syntax
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name TO username;Grant all privileges on all tables in the schema example
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO novice;Grant all privileges on all sequences in the schema syntax
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA schema_name TO username;Grant all privileges on all sequences in the schema example
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO novice;Grant all privileges on the database syntax
GRANT ALL PRIVILEGES ON DATABASE database_name TO usernameGrant all privileges on the database example
GRANT ALL PRIVILEGES ON DATABASE novicetechie TO novice;Permission to create database syntax
ALTER USER username CREATEDB;Permission to create database example
ALTER USER novice novicetechie;Make a user as superuser syntax
ALTER USER username WITH SUPERUSER;Make a user as superuser example
ALTER USER novice WITH SUPERUSER;Remove superuser status syntax
ALTER USER username WITH NOSUPERUSER;Remove superuser status example
ALTER USER novice WITH NOSUPERUSER;PostgreSQL security : Postgres grant all privileges to user on a specific database only | Postgresql database security best practices
Please visit other related articles
PostgreSQL: Add macros in PgAdminIII
PostgreSQL: Export PostgreSQL data in to excel file
PostgreSQL: How to restrict the user access to the databases in Postgresql based on the permission
PostgreSQL: List table details using Alt+F1 in PostgreSQL
PostgreSQL: How to get ROW_COUNT of last executed query in PostgreSQL like MS SQL @@ROWCOUNT ?
0 comments