In this video, we are going to see how to Grant and Revoke Privileges in PostgreSQL Server.
What is Grant?
GRANT — define access privileges.
What is REVOKE?
REVOKE — remove access privileges.
The possible privileges are:
SELECT, INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER,CREATE,CONNECT,TEMPORARY(TEMP),EXECUTE,USAGE, ALL PRIVILEGES.
Part1: GRANT Examples:
1. Grant SELECT privileges on table ‘customer’ to user ‘anil’
Continue reading Grant and Revoke Privileges in PostgreSQL
In this tutorials, we have explained how to check list of privileges on a table in PostgreSQL. We can get the privileges by using SQL statement and meta-command.
1 . SQL Statement to get list of permissions on a table.
SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name='customer'
Continue reading How to check list of privileges on a table in PostgreSQL
In this article we are going to set PostgreSQL concurrent connection limit for user. As a administrator, we have to revoke permissions from the user, set the connection limits to user and set unlimited concurrent connection limits.
Set PostgreSQL concurrent connection limit:
In this video, we have the concurrent user connection limit to none,5, and unlimited.
1. Set concurrent connection limit none.
alter user anil connection limit 0;
In this tutorial, we find certain user connected to PostgreSQL server or not by querying pg_stat_activity system view.
If below query returns any results means, then user is connected to PostgreSQL Server database.
Check whether PostgreSQL User is connected or not:
select usename,datname from pg_stat_activity where usename='anil';
Continue reading Check whether PostgreSQL User is connected or not
In this tutorial, we are going to find data directory and configuration files paths in PostgreSQL Server.
If environment variables configured, then we can get the data directory path by running below command on Linux Shell i.e.
echo $PGDATA. If it is not set then we have to execute SQL statements. 1. Find the data directory path in PostgreSQL Server.
select setting from pg_settings where name = 'data_directory';
Continue reading Find PostgreSQL data directory and configuration file location
In this article, we are going Lock and Unlock PostgreSQL User. Privileges required to Lock and Unlock PostgreSQL User is ALTER USER.
Steps to Lock and Unlock PostgreSQL User:
1. Lock the user
ALTER USER anil NOLOGIN;
In this article, we will see how to install PostgreSQL 11 on Redhat Linux Operating System.
Steps to Install PostgreSQL 11 on Redhat:
Step1: Configure PostgreSQL yum repository:
rpm -Uvh https://yum.postgresql.org/11/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Step2: Install PostgreSQL 11 Server.
Continue reading Install PostgreSQL 11 on Redhat Linux Operating System
In this article, we will see how to change owner of table in PostgreSQL. To change owner of the table, you must be owner of the table or must have alter table or superuser permissions.
It is database administrator routine task to change table owner in PostgreSQL. This can be done with ALTER TABLE statement.
Syntax to Change table owner in PostgreSQL:
ALTER TABLE public.table_name OWNER TO another_username;
Verify the owner of the table by running below command:
\dt table_name;
We can create user in PostgreSQL by using createuser program or by using create user statement. To create user in PostgreSQL server, user must have create user(createrole) privileges.
Syntax to Create User in PostgreSQL:
CREATE USER name [ [ WITH ] option [ ... ] ] where option can be: SYSID uid | CREATEDB | NOCREATEDB | CREATEUSER | NOCREATEUSER | IN GROUP groupname [, ...] | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'abstime'