Reference: https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-14-04
1. To create a user
#create user unais with password '123';
==> To make superuser
#alter user unais with superuser;
select * from sessions limit 10;
2. To create database
#create database dbname;
3. To grant all privileges on a db to a user
#grant all privileges on database dbname to dbuser;
4. To log in to a single database
psql -U unais dbname;
If peer authentication fails, edit /etc/postgresql/9.1/main/pg_hba.conf
local all postgres all
TO
local all postgres md5
Peer authentication
5. Error while loading application over browser
psql: FATAL: Peer authentication failed for user
Change in pg_hba.conf
local all all peer TO local all all trust
6. To list all the databases
# \list
6. To list all the tables
#\dt
6. To list roles
\du
6. To give permission to a user/role on a database where another user has full permission
GRANT user1 to user2;
then check using du -
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+------------
db1 | Superuser, Create role, Create DB, Replication | {}
user1 | Create DB | {}
postgres | Superuser, Create role, Create DB, Replication | {}
user2 {user1}
6. To list limited entry or rows
#select * from table_name limit 10;
7. To truncate a table
TRUNCATE TABLE table_name;
8. To list size of tables of a database in descending order
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
9. To check version
#psql -V
sudo -i -u postgres
psql1. To create a user
#create user unais with password '123';
==> To make superuser
#alter user unais with superuser;
select * from sessions limit 10;
2. To create database
#create database dbname;
3. To grant all privileges on a db to a user
#grant all privileges on database dbname to dbuser;
4. To log in to a single database
psql -U unais dbname;
If peer authentication fails, edit /etc/postgresql/9.1/main/pg_hba.conf
local all postgres all
TO
local all postgres md5
Peer authentication
The peer authentication method works by obtaining the client's operating system user name from the kernel and using it as the allowed database user name (with optional user name mapping). This method is only supported on local connections.
Password authentication
And then reload PostgreSQLThe password-based authentication methods are md5 and password. These methods operate similarly except for the way that the password is sent across the connection, namely MD5-hashed and clear-text respectively.If you are at all concerned about password "sniffing" attacks then md5 is preferred. Plain password should always be avoided if possible. However, md5 cannot be used with the db_user_namespace feature. If the connection is protected by SSL encryption then password can be used safely (though SSL certificate authentication might be a better choice if one is depending on using SSL).
5. Error while loading application over browser
psql: FATAL: Peer authentication failed for user
Change in pg_hba.conf
local all all peer TO local all all trust
6. To list all the databases
# \list
6. To list all the tables
#\dt
6. To list roles
\du
6. To give permission to a user/role on a database where another user has full permission
GRANT user1 to user2;
then check using du -
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+------------
db1 | Superuser, Create role, Create DB, Replication | {}
user1 | Create DB | {}
postgres | Superuser, Create role, Create DB, Replication | {}
user2 {user1}
6. To list limited entry or rows
#select * from table_name limit 10;
7. To truncate a table
TRUNCATE TABLE table_name;
8. To list size of tables of a database in descending order
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
9. To check version
#psql -V
