HELP WITH 'Data Exchange Service' POSTGRESQL

https://www.codecademy.com/paths/full-stack-engineer-career-path/tracks/fscp-22-advanced-postgresql/modules/wdcp-22-database-intact/projects/data-exchange-service-postgres-roles

I am completely lost when it comes to this project. The hints aren’t helpful at all, and neither are the lesson or the cheatsheets available. I’m specifically having trouble with the “Granting a Publisher Access to Analytics” portion of the project. The worst part is that there’s no accompanying video to help out the learners when they do get stuck.

9 Likes

If you have suggestions for the course I’d suggest adding them to this section of the forums Suggestions; Feature and Course Requests - Codecademy Forums, make sure to keep the URL and any useful info.

If you’re looking for assistance from other users it would help you if you break any questions down into something others can answer, How to ask good questions (and get good answers) has some good advice on this front.

  1. Although we’re designing a collaborative data environment, we may want to implement some degree of privacy between publishers.

Let’s implement row level security on analytics.downloads . Create and enable policy that says that the current_user must be the publisher of the dataset to SELECT .

The code I typed in:
CREATE POLICY emp_rls_policy ON analytics.downloads
FOR SELECT TO publishers USING (publisher=current_user to return rows);

ALTER TABLE publishers ENABLE ROW LEVEL SECURITY;

I am given this error:
ERROR: syntax error at or near “CREATE”
LINE 3: CREATE POLICY emp_rls_policy ON analytics.downloads
^
ERROR: relation “publishers” does not exist

I was given this error earlier in the lesson…where am I going wrong?

Thanks for your answer!

1 Like

I think there are just some buggy parts in this module, I’ve gotten errors throughout the SQL sections with the correct syntax time and time again.

2 Likes

So analytics is a schema, and in order to grant access to the schema you will need to say so in the statement. For this exercise, for example, your statement would read.

GRANT USAGE ON SCHEMA analytics TO publishers;
2 Likes

I also noticed that once i had created the role ‘publishers’ and then went and deleted that line of code, i received that error.

In order to keep the publishers role existing, keep the create role statement line of code and don’t delete it, this should remove that error at least

“publishers” is not a table, it’s a group. You want to include the table you are creating the policy for in your ALTER TABLE statement.

You should take a look at your USING condition, too. The analytics.downloads table doesn’t have a column called “publisher”. You want to find a column in that table which corresponds to a publisher.

1 Like

SELECT rolname

FROM pg_roles;

SELECT *

FROM pg_roles;

SELECT current_user;

CREATE USER abc_open_data WITH LOGIN NOSUPERUSER;

CREATE ROLE publishers WITH NOSUPERUSER ROLE abc_open_data;

GRANT USAGE ON SCHEMA analytics TO publishers;

GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO publishers;

SELECT *

FROM information_schema.table_privileges

WHERE grantee = ‘publishers’;

SET ROLE abc_open_data;

SELECT * FROM analytics.downloads limit 10;

SET ROLE ccuser;

SELECT *

FROM directory.datasets;

GRANT USAGE ON SCHEMA directory TO publishers;

GRANT SELECT (id, create_date, hosting_path, src_size, publisher) ON directory.datasets TO publishers;

SET ROLE abc_open_data;

SELECT id, publisher, hosting_path

FROM directory.datasets;

SET ROLE ccuser;

CREATE POLICY policy_abc ON analytics.downloads FOR SELECT TO publishers USING(owner=current_user);

ALTER TABLE analytics.downloads ENABLE ROW LEVEL SECURITY;

SET ROLE abc_open_data;

SELECT * FROM analytics.downloads limit 10;

12 Likes

Nice first post! This was really helpful where the instructions were a bit hard to follow. For future reference you can use the small </> button in the editor and then type or paste the code into the code-block.

1 Like

Thanks for your comment. Unfortunately adding code feature </> does not support postgresql. It only supports programming languages like python, javascript, etc.

SELECT rolname FROM pg_roles;
SELECT * FROM pg_roles;
-- And so on..

The SQL formatting perhaps isn’t great but I tried it above and it is an improvement over just pasting it in without using the </> feature. Anyway, thanks for providing your solution!

People that are having trouble with the end problems, The phrasing on what is desired for the Row Level Security question is incorrect. I believe they desire you to set the owner = current_user. At least my code worked appropriately when I did that instead of publisher = current_user. Hopefully helpful.

1 Like

I know this is from a while back, but I hope this helps someone in the future. I believe there is an error in the wording of the steps.

The analytics.downloads table does not have a “publisher” column, which is why the “publisher” does not exist error occurs. I think the actual wording of the step should be:
“Create and enable policy that says that the current_user must be the owner of the dataset to SELECT .”

My solution code:
CREATE POLICY privacy ON analytics.downloads FOR SELECT
TO publishers USING (owner=current_user);

ALTER TABLE analytics.downloads ENABLE ROW LEVEL SECURITY;

I find the backend development class to be full of bugs and miscommunications. Very frustrating after having such a positive experience with the front end development class, for the most part (some areas near the end were a little messy).

2 Likes

So forgive me if this is simply my ignorance, but when was this covered in the lesson…? Because I have literally copypasted notes at every stage and SELECT ON ALL TABLES is mentioned exactly nowhere - so I’m confused as to why it’s suddenly asked for with nothing even mentioned about it in the hint

This project seems incredibly badly explained and it’s far from the first time

3 Likes

Interestingly, the hint for step 5 says to :

Fill in the SQL below to create the group role described in the prompt. “CREATE ROLE publishers WITH ____ ____ ROLE abc_open_data”

The two blank options in the hint initially coerced me to go with “WITH NOSUPERUSER IN ROLE abc_open_data”, which was ultimately wrong.

I believe this leads to the new role, “publishers”, becoming a member of “abc_open_data”, rather than the other way round.

I received this “ERROR: permission denied for schema analytics” on step 9 until reading the solution from nidhishgajjar and removing “IN” from the statement.

Hopefully this helps anyone who made the same mistake.

1 Like

It’s 2023 and the wording issue is still present…I also figured out that the issue comes from “publisher” column not existing and in turn, we should choose “owner”

1 Like

This one was frustrating. I’ve enjoyed the rest of the SQL section for the most part though.